Create an Index Column for Subgroups in Power Query

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
- - - - Blog - - - -
www.goodly.co.in/

[@GoodlyChandeep]

Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

[@imronnesia5170]

Love the way you explain everything. You always make some complex things become a simpe thing with simple and easy explanation.

[@SaniGarba]

One word, "Ëxcellent". I've seen a few approaches to this problem, yours is the most ingenious by its simplicity. Thanks.

[@venkateshpandian9771]

Very useful trick. I always admire, your videos are in a simple language and easy to understand and digest. Thanks for sharing your knowledge.

[@thamirlnaachiyaar5153]

I greatly appreciate it, it helped me to reconcile accounts with many transactions having the same value. I was struggling to work it out to eliminate the duplicate. 🙏🙏 as the query picking up all rows with the same value instead of one.

[@FutureFaz]

I used this to create dropdowns for a boatload of data options based on the first column. Thank you!

[@user-jk1dl5or3r]

This worked beautifully!! Thank you!

[@Kurt0542]

Exactly what I needed, would like twice if possible!

[@kiadyandrianantoandro5871]

Daim man you just saved me millions of hour I cannot expresse how thankful I am !

[@GoodlyChandeep]

Glad it helped!

[@sawanb2337]

Very useful Tip, thank you.

[@juansepowerplatform]

Thanks for your videos, best resource to really grasp M code.

[@GoodlyChandeep]

You're welcome!

[@CarlosRomero-fb9pj]

Thank you man! Great video.

[@jasonswami9275]

Clear and concise. An excellent tutorial🙏🙏🙏

[@GoodlyChandeep]

Glad you like it 😊

[@paulkogels6288]

Very helpful. Very clear explanation. Thank you.

[@GoodlyChandeep]

Glad it was helpful!

[@pbihari0214]

good video, thank you.

[@sjh1956]

Thx for this video, easy to understand. I do have another approach though (may work better with 'smaller' datasets. I use a simple formula. In cell L3: COUNTIF($J$3:J3,J3). Copy down. Cell L4 should read COUNTIF($J$3:J4,J4) and so on.

[@Ratnakumarwrites]

Hooray Man... U r there for my problems & I stuck.

[@sunnygala7972]

Thanks Chandeep! Really love your teaching approach. Cheers

[@GoodlyChandeep]

Glad to hear that!

[@algawzy]

You are perfect I spend many times to reach the solution 👌

[@GoodlyChandeep]

Glad to hear that!

[@tasoine]

THANK YOU MAN!

[@thekochava]

thank you!

[@SamehRSameh]

Great 🎉

[@gennarocimmino]

kudos for the clarity! Suppose I need to pick/select the first "n" ranked people in each subgroup (not the first two, not last three, etc.). Let's say that I have a number of gifts/prizes to provide to each subgroup: to the first subgroup I will award two gifts, to the second subgroup I will award three gifts, to the third subgroup one gift, etc. The number of gifts/prizes to be assigned to each subgroup is a second available table. So, after creating the subgroup index, I am only interested in seeing/selecting from that particular subgroup a number of records equal to the number of available gifts/prizes from the other table. Can this be done? Namaste!

[@tofuboyvn]

great video, you helped me solving my issue. but i have a question. After grouping and attribute the index for each table , can we export the table into many excelsheet for each group ? and how ?

[@tasoine]

Thanks!

[@GoodlyChandeep]

Welcome! Appreciate the tip !

[@aas2943]

dis was goodd.....how to build gradient fill curved area chart in power bi

[@rupalmukhi1254]

Nice video. How to compare the values for each subgroup at Index 1and 2 ?

[@ashithachillara2196]

If we have multiple dimensions (Category, Sub-category, items) then how can we give index for category column.

[@Alan.DL7]

Awesome resource. Quick question though, is there any reason why NOT to use the 2 arrows on the header to expand the table instead of writing the code as you did? The end result is the same but maybe I am missing something here. Cheers!

[@joevanbedico-cn1rq]

Not sure if u already found the answer to ur question, but in case u don't, the reason is that when u expand ur column using the button it will hardcode each of the column name in the step, telling ur query to only expand those column, losing the ability to capture the additional column that is/are maybe added in the future, while Table.Combine will always captures it and expand all columns of that table. It depends on ur preference though.

[@Alan.DL7]

@@joevanbedico-cn1rq Hello mate! Thanks for the answer but yes, I have found my answer and learned long ago how to dynamically expand tables. Is something I use in all my queries even if I know that it will be pretty unlikely that a new column or change column name will happen. I always try to avoid hardcoding codes as much as possible :) Thanks again nonetheless for taking the time!

[@kazanduryI]

yeahh you ar a beast!!

[@GoodlyChandeep]

Thanks!!

[@phoolkhan87]

Simply WOW.... Could you pls help me In case i Wana select the max. Index of each category. I.e. a5 or b3 and c2?

[@eduardoassis1449]

Thank you very much for all your work!

[@mohammadrizeg]

Wooow, Thanks a lot. I have one question, how to sort underlying table descending?

[@davidlopez-fe2lb]

If you sort the value column before this step, you should be able to achieve what you want. In this video he just randomly assigned an Index value to a category based off of the value column, but often times people want to sort the values then add an index column.

[@syedawajeehaali9693]

Hi, could anyone assist. How could we group repeated data into desired category in power query. I found this type of functionality in Power BI in visual but couldn't in power query

[@GosCee]

Chandeep, how do you write the code when some rows are blanks?

[@sawanb2337]

I was just wondering if possible how to add another column next to Serial number and to count the number of rows based on the category column please? ie A = 5, etc

[@manuc5353]

Saviour!

[@kkhalidabed]

Cleaver Approach