cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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?

49 REPLIES 49
Frequent Visitor

You are a genious!!! Saved lots of time . Thanks for the soultion

Helper I
Helper I

Hi. I'm trying to do something different about the cases exposed. 

 

I have a table that need an increase index when I have some change between three columns. I mean: 

 

WorkerFechaValorIndex
A16/04/2018TRUE1
A16/04/2018TRUE1
A16/04/2018FALSE2
A16/04/2018TRUE3
B15/04/2018TRUE4
B16/04/2018FALSE5
B16/04/2018TRUE6
B16/04/2018TRUE6

 

Starting by 1 in each change of first 3 columns I need to increase the index. I'm trying with all the answer from this post but I can't find the solution. 

 

Other possibility is to save the last combination and the last index in a parameter or another table, but I can't find how to save it in a parameter or table. 

Helper I
Helper I

Hi. I'm trying to do something different about the cases exposed. 

 

I have a table that need an increase index when I have some change between three columns. I mean: 

 

WorkerFechaValorIndex
A16/04/2018TRUE1
A16/04/2018TRUE1
A16/04/2018FALSE2
A16/04/2018TRUE3
B15/04/2018TRUE4
B16/04/2018FALSE5
B16/04/2018TRUE6
B16/04/2018TRUE6

 

Starting by 1 in each change of first 3 columns I need to increase the index. I'm trying with all the answer from this post but I can't find the solution. 

 

Other possibility is to save the last combination and the last index in a parameter or another table, but I can't find how to save it in a parameter or table. 

 

In Excel is very easy because yo can calculate from the information in the last cell, but here I can't. 

 

The excel formula could be: 

 

=SI(A2=A1;SI(C2="FALSE";SI(C1="FALSE";E1;E1+1);SI(C1="FALSE";E1+1;E1));E1+1)

 

 

 

Yes, that's possible. No use for the grouping in this case, as your index shall continue to run. The only thing you need here is a reference to your previous row. For performance reasons, I recommend this method:

https://www.youtube.com/watch?v=xN2IRXQ2CvI

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

Anonymous
Not applicable

saving this jewel to favorites!

Frequent Visitor

Awesome. Great video. The video definitely helped explain things. Thanks

Hi @ImkeF 

I experimented your method to rank my data with two criterion. However, when I took a look at the data carefully, I found some data which is not ranked properly.

 

 WEIGHTHEIGHTRANKCORRECT RANK
A651.723
B651.832
C701.7511

A,B,and C three people are ranked firstly by weight, if some of them have the same weight then we will rank them by height. I got the rank column which is not correct. Here I also show you the correct ranking.

 

So I tried to the buffer table query to conserve the sort, unfortunately, the issue has not gone so far. 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK00DMw0TMyMDRXitXBKWRkjCFkYKhnYIoQcgKpMkRRhUPI2ACvUCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    RemoveDups = Table.Distinct(#"Changed Type", {"Group", "Date"}),
    Partition = Table.Group(RemoveDups, {"Group"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index",1,1), type table}}),
    LookupTable = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"}),
    Lookup = Table.NestedJoin(#"Changed Type",{"Group", "Date"},LookupTable,{"Group", "Date"},"NewColumn",JoinKind.LeftOuter),
    ExpandedIndex = Table.ExpandTableColumn(Lookup, "NewColumn", {"Index"}, {"Index"})
in
    ExpandedIndex

This is your query. The difference is that I rank based on two values - weight and height. Also, I consider merge later, as I have data which has the same weight and height, so I want them to have the same ranking. I used buffer table after Partition to fix the issue, the issue is still there.

 

I suspect that in the Partition, they sort weight and height as any type of numbers, because later when I expand the table (in the LookupTable step), the column weight and height come with any data type.  

 

How do you view this case?

 

I appreciate any help.

 

Thank you.

 

Best, Qianru

 
Frequent Visitor

 

This site may help. Even though it is power query the same techniques that use M code/DAX applies to Power BI. Look around the 20min mark in the video. It shows how to rank the same if the same.

 

 

Leroy_0-1597272060954.png

 

 

23:03 / 30:42

MSPTDA 08: Power Query Group By feature & Table.Group Function

 

https://www.youtube.com/watch?v=hs21s0TWT14

 

I also recreated your data and added a few in case the second sort, by height also had a ‘match’

Leroy_1-1597272060958.png

 

 

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Weight", Int64.Type}, {"Height", type number}}),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Weight", Order.Descending}}),

    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "IndexWeight", 1, 1),

    #"Sorted Rows1" = Table.Sort(#"Added Index",{{"Height", Order.Descending}}),

    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows1", "IndexHeight", 1, 1),

    #"Grouped Rows" = Table.Group(#"Added Index1", {"Weight"}, {{"All Rows", each _, type table [Person=text, Weight=number, Height=number, IndexWeight=number, IndexHeight=number]}, {"Rank Weight", each List.Min([IndexWeight]), type number}}),

    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Person", "Weight", "Height", "IndexHeight"}, {"All Rows.Person", "All Rows.Weight", "All Rows.Height", "All Rows.IndexHeight"}),

    #"Grouped Rows1" = Table.Group(#"Expanded All Rows", {"All Rows.Height"}, {{"Height", each _, type table [Weight=number, All Rows.Person=text, All Rows.Weight=number, All Rows.Height=number, All Rows.IndexHeight=number, Rank Weight=number]}, {"Rank Height", each List.Min([All Rows.IndexHeight]), type number}}),

    #"Expanded Height" = Table.ExpandTableColumn(#"Grouped Rows1", "Height", {"Weight", "All Rows.Person", "All Rows.Weight", "All Rows.Height", "All Rows.IndexHeight", "Rank Weight"}, {"Height.Weight", "Height.All Rows.Person", "Height.All Rows.Weight", "Height.All Rows.Height", "Height.All Rows.IndexHeight", "Height.Rank Weight"}),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded Height",{"Height.All Rows.Weight", "Height.All Rows.IndexHeight", "Height.All Rows.Height"}),

    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Height.Rank Weight", "Rank Weight"}}),

    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Height.All Rows.Person", "All Rows.Height", "Height.Weight", "Rank Weight", "Rank Height"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"All Rows.Height", "Height"}, {"Height.Weight", "Weight"}}),

    #"Sorted Rows2" = Table.Sort(#"Renamed Columns1",{{"Rank Weight", Order.Ascending}, {"Rank Height",Order.Ascending}}),

    #"Reordered Columns1" = Table.ReorderColumns(#"Sorted Rows2",{"Height.All Rows.Person", "Weight", "Height", "Rank Weight", "Rank Height"})

in

    #"Reordered Columns1"

 

Result as below

 

Leroy_2-1597272082435.png

 

I hope this helps

 

Frequent Visitor

Someone might be able to make the code a bit tidier

Hi @Leroy 

 

Thank you for your answer, but I think I am not clear about my question. I want to evaluate both criterion and create one ranking. 

 

In the example, it should be like that:

 

qsong_0-1597324484996.png

Does it make sense now? @ImkeF provided us with a solution for it in the blog, my issue is that the ranking is not proper for some observations, so my result may look like that:

 

qsong_1-1597324755145.png

Thank you, I appreciate any ideas to fix it.

 

Best, Qianru

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors