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.
Appreciate this and a good trick, thank you! However, I tried this to create index for more than two levels, eg. three - category & subcategory & subcategory's subcategory. Unfortunately I am unable to get it to work and also all the other solutions are dealing only with two levels. e.g. I have these values in three columns CatA | SCatA | Scat2A CatA | SCatA | Scat2B CatB | SCatA | Scat2C CatC | SCatB | Scat2X CatC | SCatB | Scat2Y Indexes should be as follows: 1 | 1 | 1 1 | 1 | 2 2 | 1 | 1 3 | 1 | 1 3 | 1 | 2 No combination of group by and creating the custom columns work. Any guidance?
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!
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.
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 ?
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!
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.
@@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!
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
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.
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
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
One word, "Ëxcellent". I've seen a few approaches to this problem, yours is the most ingenious by its simplicity. Thanks.
Love the way you explain everything. You always make some complex things become a simpe thing with simple and easy explanation.
I used this to create dropdowns for a boatload of data options based on the first column. Thank you!
Very useful trick. I always admire, your videos are in a simple language and easy to understand and digest. Thanks for sharing your knowledge.
Daim man you just saved me millions of hour I cannot expresse how thankful I am !
Glad it helped!
Amazing content, saved my work day :)
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.
Very Helpful, Thanks
Exactly what I needed, would like twice if possible!
Thanks Chandeep! Really love your teaching approach. Cheers
Glad to hear that!
You are perfect I spend many times to reach the solution 👌
Glad to hear that!
Clear and concise. An excellent tutorial🙏🙏🙏
Glad you like it 😊
Very helpful. Very clear explanation. Thank you.
Glad it was helpful!
Hooray Man... U r there for my problems & I stuck.
This worked beautifully!! Thank you!
Appreciate this and a good trick, thank you! However, I tried this to create index for more than two levels, eg. three - category & subcategory & subcategory's subcategory. Unfortunately I am unable to get it to work and also all the other solutions are dealing only with two levels.
e.g. I have these values in three columns
CatA | SCatA | Scat2A
CatA | SCatA | Scat2B
CatB | SCatA | Scat2C
CatC | SCatB | Scat2X
CatC | SCatB | Scat2Y
Indexes should be as follows:
1 | 1 | 1
1 | 1 | 2
2 | 1 | 1
3 | 1 | 1
3 | 1 | 2
No combination of group by and creating the custom columns work. Any guidance?
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!
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.
Very useful Tip, thank you.
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?
Thanks for your videos, best resource to really grasp M code.
You're welcome!
Thank you man! Great video.
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 ?
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!
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.
@@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!
If we have multiple dimensions (Category, Sub-category, items) then how can we give index for category column.
dis was goodd.....how to build gradient fill curved area chart in power bi
Great 🎉
Nice video. How to compare the values for each subgroup at Index 1and 2 ?
THANK YOU MAN!
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
Chandeep, how do you write the code when some rows are blanks?
Wooow, Thanks a lot. I have one question, how to sort underlying table descending?
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.
good video, thank you.
Thanks!
Welcome! Appreciate the tip !
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
thank you!
Thank you very much for all your work!
yeahh you ar a beast!!
Thanks!!
Saviour!
Cleaver Approach