Create Index Column By Group in Power Query

[@BIGorilla]

To learn more about the syntax of Table.AddIndexColumn function, make sure to check out: powerquery.how/table-addindexcolumn/

[@jist6953]

OMG, THIS, THIS, THIS...I needed THIS! Thank you. Subscribed!

[@ДенисДементьев-т3о]

Dear BI Gorilla, your way of explanation is extremely clear and understandable. Thank you very much for video.

[@magnuskrook5618]

🎯 Key Takeaways for quick navigation: 00:00 🧮 *Introduction to Adding Index Numbers in Power Query* - Discussing how to add index numbers to queries in Power Query for single and multiple groups. - An index number is a sequential number starting at 1, increasing for each item in a group. - Example shown with two main color groups: "other" and "popular." 02:09 🔄 *Methods for Adding Index Numbers to Grouped Data* - Demonstrating different methods to add index numbers to grouped data in Power Query. - The first method involves using a custom column and adjusting code to reference table rows in a column. - Second method streamlines the process by directly adding an index column to grouped data. 05:59 🔢 *Considerations for Data Order in Indexing* - Importance of sorting data correctly before grouping and indexing. - Demonstrates how changing the sorting order affects the index numbers assigned to different groups. - Example with color groups shows the impact of sorting order on index allocation. 07:35 🆕 *Additional Method for Grouping and Indexing* - Introducing another method for adding index numbers to grouped data. - This method involves adjusting the grouping operation in Power Query. - Highlighting the flexibility of Power Query's 'M' language for data manipulation. Made with HARPA AI

[@viniscera-97]

Thank you from Brazil! This solution solved my problem

[@FRANKWHITE1996]

To honor this film I've called one step of my query "bi gorilla" ❤

[@alexrosen8762]

Very useful tutorial. Many use cases for example to create unique index numbers for rows in different segments.

[@BIGorilla]

Thanks Alex, I'm glad the video is well received. I use this trick a lot and hope it serves you as well as it does for me. 😁

[@dmederos13]

2:35am and can't keep watching your videos🤣 This one is super handy, I feel I'm getting closer to the solution I want to implement!

[@vaigundhansridhar6745]

this was amazing! Just what was needed while solving a tough use case. Thank you :)

[@fajnefajne4339]

I love this comprehensive explanation. Thank you. I feel like in the supermarket. I have a choice and I can pick up the solution I like 😊

[@raulparra60]

Gracias por compartir. Como siempre muy util este tutorial. Saludos desde Bogota - Colombia

[@BIGorilla]

Mi placer!

[@williamarthur4801]

I'll Look forward to it.

[@FabianH-zs1go]

Great Video, exactly what I was looking for! Thanks.

[@InventarioGintracom]

I was searching this, and i did not find any video who can explain it in spanish, thank you!

[@ThamerAffara]

Thank you for this wonderful easy to follow!

[@adrianacorrea5634]

Yours videos are great. Thanks

[@willvanluven2769]

Awesome! True BI hero!

[@MaydayAggro]

Took me forever to ask a question of Google the right way to get this video as an answer. I need to reconcile accounts with thousands of transactions per month and sometimes dozens of repeated amounts per day. Creating an index for each amount makes the job much easier, especially since my two queries do not have any like columns other than amount.

[@piterkub]

Same here

[@NSLABTUTORIAIS]

Thank you very much. It was what I was looking for.... Muito, muito legal!!!!!

[@brij26579]

Awesome 👌

[@BIGorilla]

Thanks ^^

[@martyc5674]

Very useful 👍

[@BIGorilla]

Thank you!

[@williamarthur4801]

I enjoyed the last version as it brought up the index not showing in expand, I've always just removed everything after each and // Table.AddIndexColumn( _ , "Index", 1,1, Int64.Type) }} ) // which doesn't cause the problem of not showing index , it's not something I'd considered and it's good to be aware of these things. I enjoy your relaxed manner very much.

[@BIGorilla]

Just wow, thanks for your kind words William, I really appreciate it. These are things I've only recently learned about. After recording this video, I've delved deeper into this topic. And something I learned is how the 'type table' always allows you to indicate column types. From the sounds of it you seem interested in the topic, so please allow me to share my latest blogpost on this: gorilla.bi/power-query/creating-tables/ You will find plenty of examples that also include the relevant syntax for defining column types. Hope that helps. Enjoy Power Query! Rick

[@williamarthur4801]

@@BIGorilla Thank you , yes I am interested , it's all the small things, i.e; I was recently using List.Accumlate to create a running total the seed is in curly brackets and I've still not really understood why apart form it's a list thing.

[@BIGorilla]

Interesting. That's a topic I haven't delved into. It will be one of my coming blogposts, just keep an eye out :)

[@GabrielSantos-i4x]

U just saved my day! Thankssss

[@ninokereselidze5848]

Great, thank you!

[@nancyxu4029]

Great! That's super helpful. Thanks!

[@chrisbrindle6298]

Excellent! Thanks man!

[@smartwork4768]

Nice thank you

[@BIGorilla]

You’re welcome ☺️

[@arnold5621]

Thank you so much! Exactly what I've been looking for 😍

[@BIGorilla]

Great to hear that Arnold 🔥🙏

[@matheusgameiro61]

Useful bro! A manager just got mass clients transfer to his wallet, then asked me for help to get an overview about his "new wallet" what they have in pipeline, lost/cancel history and the active contracts. I needed to create a column with the most recent 3 contacts from the account, first i ordered desc, then used index and finally filtered index = 0 or 1 or 2 (top 3) ;) tks

[@julliettecarignan8563]

I'm curious what would be some use cases why there'd be value in having separate index sequences for the various groups? This is useful, thank you, just trying to figure out what requirement this helps us deliver (even if an intermediate one).

[@chrismcgowan1078]

I'm using index as primary key for a dataset (due to duplicates rows which are correct due to how the project operates). The number of rows stays the same every week but dates are updated within the dataset. I'm taking weekly copies of this dataset so that I can load them all with a date stamp column and compare week on week progress. To avoid manually adding the index column every week, grouping by date then adding the index should still create the same primary key for every row consistently each week.

[@pieterlinden8292]

Oh, that's super handy! It lets you do the equivalent of ROW_NUMBER() OVER (PARTITION BY...) in T-SQL, but without having to import your data into SQL Server first! Then you can do things like TOPN, but at the data source level.

[@JustMe-sh8nd]

gast, wauw precies wat ik nodig had, 1 tip toch, je subjects van je videos geven niet weer waar noobs op zoeken, ik zocht bijvoorbeeld op "index based on duplicate' dan kom je met deze video niet naar voren. het is dat ik geabonneerd ben en in jou lijst aan het scrollen was.

[@jafarhussain4665]

Hi @bigorilla, Thank you for this Update, I was creating Index based on each category but before that I Did soring based on date column so that index should be done based on the sorted date. after following your procedure, id did created Index but not based on the sorted dates., it created randomly. can you please provide some solution for this

[@Kaushal_Khunt]

Thank you Bro

[@Rice0987]

Oh, a bit late i've saw this video, cause i forgot a case i wanted to apply this! :( So, i need to remember this method at least for later, may be i'll remember my case. :) THANKS, Rick!

[@BIGorilla]

That’s great John, if you find the case let me know!

[@minadev1]

I am an accounting person and wanted to learn Power BI, should I start working on M language too?

[@g5scosandygoodwin]

@BiGorilla - This is great, but it defines the index based on Column 1 and then Column 2. However, how can the Index be added based on Column 1 and Column 3 without reordering the columns. Column 1 (Color Group) and Column 3 (SalesAmount) would be a more logical indexing in your data set.

[@minadev1]

i did not quite get method 02 for indexing columns after dividing by group

[@williamarthur4801]

Re accumulate , this is slightly different to what I found online and cuts out some of the steps, add index to source, ; then insert step; Table.AddColumn(#"Added Index", "RTC ", each List.Accumulate ( #"Added Index" [Units], {0} , (Step, Current ) => Step & { List.Last(Step) + Current } ) {[Index]}) I've never really got to the bottom of the ampersand, but then I could never have crated the List.Generate Febenoci sequence either, another really good video.

[@BIGorilla]

Hey William, The ‘step’ part is the value that changes by list accumulate. List accumulate takes the function in argument 3 and applies it for n number of times. N is the number of items in the list of argument 1. What the ampersand does, is it concatenates the value of ‘step’ (which is a list) to the newly transformed value in the {} list next to it. I’m quite on the way with the article. The written version should be out by next week :)

[@alexromera6140]

THANKS!

[@shivamkapoor1827]

How can we do multiply two columns from different tables using custom function in power query??

[@jaggyjut]

What if we want to reset the index end of the month. I have IT helpdesk data and like to calculate the avg number of tickets per month and year

[@BIGorilla]

In this case it sounds easier to group your data by Month-Year, and as aggregation column create columns with: - Count the number of rows that's grouped. - All Rows - Creates a table object with the grouped rows. Unpack this object and you should be good to go. You will have all your data including the a line with the number of items in the month :)

[@ansonkong3813]

I like the first method

[@ExcelInstructor]

I stopped using the expand option long long time ago, its faster to convert the Column with Table objects to a list and then using Table.combine fonction on top of that list result of tables.

[@Rice0987]

I always have to sort rows in columns after some steps, sometimes several times to get exactly what i want.

[@BIGorilla]

You can sort the column before doing the group by. That should already work. In case it doesn’t work, you can wrap the table before the group by operation, in the Table.Buffer function. This forces the order to remain the same ☺️

[@HudaNurhuda-c7j]

In my case, I'm using a calculated table, but the issue is that calculated tables don't show up in Power Query.

[@dirkstaszak4838]

I reckon number 3 is my favourite one

[@SamehRSameh]

How can make this but categorized on 2 level Exambe to clarify: France lion 1 France lion 2 France paris 1 France paris 2

[@nicolasplanchon9986]

Great content! Would you know how to add an IF condition to the AddColumnIndex by any chance please ? Instead of iterating at every row, I would like my index to iterate only when the value of a given column changes (Source.Name in this case). Here is my current code : #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Source.Name", type text}, {"Team", type text}, {"Project category", type text}, {"Project type", type text}, {"Role", type text}, {"Month", type text}, {"Value", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number, Index=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "#FCST", 1, 1)), This is working well but I would like to pimp my #FCST column to have the kind of index I'm looking for. Would you have any clue about this please ? Thanks in advance!

[@nicolasplanchon9986]

Hi, I finally solved my issue with this trick : #"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number, Index=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddRankColumn([Count], "#FCST", {"Source.Name", Order.Ascending}, [RankKind = RankKind.Dense])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Project type", "Role", "Value", "#FCST"}, {"Project type", "Role", "Value", "#FCST"}),