cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fedpar
Microsoft
Microsoft

Custom column Index or Ranking by other column

I'm looking to add an index column, but have it increase according to a certain column value. Let me give an example; let's say my data is:

Group     Date
A18-Apr
A19-Apr
A23-Apr
A1-May
B21-Apr
B21-Apr
B30-Apr
B4-May

 

And I would like to have the indices show like this:

Group       Date            Index
A18-Apr1
A19-Apr2
A23-Apr3
A1-May4
B21-Apr1
B21-Apr2
B30-Apr3
B4-May4

How can I perform this dynamically?

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Thats like an index on a table partition. You can create that by using grouping on the column and returning "_" - which means that all column of the table (but only for the specific value in the column) will be return. You then nest your Index-command in:

 

let
    Source = Table1,
    Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded Partition"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

49 REPLIES 49
dong
Regular Visitor

Hi ImkeF,

Your solution works great! You are so smart! Thank you!

runski
Frequent Visitor

Excellent solution - and thanks for uploading the video.

 

Just one question, how can I choose which column to rank on?

 

Runar Wigestrand

Not sure if I understood you correctly, but I think you mean the field/column to group on? In this example it was also called Group:

 

Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

nsrshkh
Frequent Visitor

You are a genious!!!

runski
Frequent Visitor

Hi, Imke.

 

Thanks for the quick reply. I was thinking more about if you have duplicates and want to keep only the newest record based on date. Then I want to group on one column and rank on another column ( for example the date column). 

 

Something like this:

 

Group on SerialNum, rank on date column

 

Snap_2017.02.14_09h33m50s_007_.jpg

 

 

 

 

 

 

 

 

 

 

Thanks.

 

Runar

 

Oh, I see: You need to fold in a Table.Sort-operation that will be executed before the Index is added. Sth like this:

 

Partition = Table.Group(Source, {"SerialNum"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date_Updated", Order.Ascending}}), "Index",1,1), type table}}),

Does that work for you? 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

runski
Frequent Visitor

Hi, Imke.

 

Perfect! 

Thank you!

 

Runar Wigestrand

YES!!! Imke you are an absolute star 🙂

 

I have been stuck on this for weeks and weeks.  The solution you have provided works perfectly.  Thank you so much!  What a great way to start the day...

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors