rank in powerquery with criteria

[William_Moreno]

Hi everybody, I've been facing problem to create a rank in PowerQuery (m-language) with criteria.

Could you help me?

Table1

Data	Unit	Sku	Qty	Rank
2021-02-01	New York	100500	100.5
2021-02-01	New York	100600	95.0
2021-02-01	New York	100700	90.0
2021-02-01	Los Angeles	100500	110.5
2021-02-01	Los Angeles	100600	100.0
2021-02-01	Los Angeles	100700	50.0

Expected table

Data	Unit	Sku	Qty	Rank
2021-02-01	New York	100500	100.5	1
2021-02-01	New York	100600	95.0	2
2021-02-01	New York	100700	90.0	3
2021-02-01	Los Angeles	100500	110.5	1
2021-02-01	Los Angeles	100600	100.0	2
2021-02-01	Los Angeles	100700	50.0	3

Thanks in advanced.

William M.

[v-yangliu-msft · Accepted solution]

Hi  @William_Moreno ,

1. Open the Advanced Editor and place the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNI1MFTSUfJLLVeIzC/KBjINDQxMDQwgDD1TpVgdvErNwEotTfUMCKk0h6g0wKbSJ79YwTEvPTUntRjFBYZYXYCh2gzuXmLMhjjEFKw4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t, Unit = _t, Sku = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type date}, {"Unit", type text}, {"Sku", Int64.Type}, {"Qty", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unit"}, {{"flag", each _, type table [Data=nullable date, Unit=nullable text, Sku=nullable number, Qty=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([flag],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Data", "Unit", "Sku", "Qty", "Index"}, {"Custom.Data", "Custom.Unit", "Custom.Sku", "Custom.Qty", "Custom.Index"})
in
#"Expanded Custom"

2. Result.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@William_Moreno , I think this video from Curbal can help. - Add index to subgroups in Power Query

https://www.youtube.com/watch?v=7CqXdSEN2k4