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

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

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

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

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!

Thank you from Brazil! This solution solved my problem

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

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 😊

Thank you for this wonderful easy to follow!

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

I'll Look forward to it.

Yours videos are great. Thanks

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

U just saved my day! Thankssss

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.

Excellent! Thanks man!

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

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

Great! That's super helpful. Thanks!

Awesome! True BI hero!

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

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

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.

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.

THANKS!

Thank you Bro

Nice thank you

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.

Awesome 👌

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

Very useful 👍

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

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

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!

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

I like the first method

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).

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

good morning, excellent work. what I need instead is that if the hill color group is popular and the color is black the index is one, if the two values ​​are equal, then popular and black in the next row the index is always 1 and so for the other rows . I look forward to seeing you post a similar video

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.

I reckon number 3 is my favourite one

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

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

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

🎯 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

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.

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!