cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
William_Moreno
Helper II
Helper II

rank in powerquery with criteria

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

Could you help me?

 

Table1

DataUnitSkuQtyRank
2021-02-01New York100500100.5 
2021-02-01New York10060095.0 
2021-02-01New York10070090.0 
2021-02-01Los Angeles100500110.5 
2021-02-01Los Angeles100600100.0 
2021-02-01Los Angeles10070050.0 

 

Expected table

DataUnitSkuQtyRank
2021-02-01New York100500100.51
2021-02-01New York10060095.02
2021-02-01New York10070090.03
2021-02-01Los Angeles100500110.51
2021-02-01Los Angeles100600100.02
2021-02-01Los Angeles10070050.03

 

Thanks in advanced.

William M.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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.

vyangliumsft_0-1627888387310.png

 

 

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

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

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.

vyangliumsft_0-1627888387310.png

 

 

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

View solution in original post

amitchandak
Super User
Super User

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

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!