Forum Discussion

jeamps01's avatar
jeamps01
New Member
4 years ago
Solved

Advanced Rank in Power Query

Hello! How can I do this in the Power Query Editor? I need to create a ranking per month of certain values. Here's the table I'm trying to recreate. I need to calculate the Ranking column.    Da...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    Hi jeamps01 

     

    Download example PBIX file

     

    This query does it

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEyNDBVitVBEzJGF7LAUGQJETFC1maOKWSBLmSJKWKmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Date", Order.Ascending}, {"Index", Order.Descending}}),
    #"Grouped Rows" = Table.Group(Table.Buffer(#"Sorted Rows"), {"Date"}, {{"Count", each _, type table [Date=nullable date, Index=nullable number, Index.1=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index.2", 1, 1, Int64.Type)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index", "Index.1", "Index.2"}, {"Index", "Index.1", "Index.2"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Date", Order.Ascending}, {"Index.1", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Index.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Index.2", "Ranking"}})
in
    #"Renamed Columns"

     

     

     

     

    Regards

     

    Phil

cris007's avatar
cris007
Frequent Visitor
3 years ago

Hello all, 

 

Is there a way to achieve the same but only with the function table.addrankcolumn rather than using Group by and Index Column? 

 

The reason I am asking is because if there is a tie, I should get the same position for the values with a tie. The index column from the power query will not give me this result. 

 

For example, below, the two first lines rows should get the same ranking based on Index: 

Date Index Ranking
1/1/2021 105 1
1/1/2021 105 1
1/1/2021 85 3
1/1/2021 95 2
2/1/2021 107 2
2/1/2021 108 1

 

Anyone knows how to do this ? 

Thanks