Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
@William_Moreno , I think this video from Curbal can help. - Add index to subgroups in Power Query
https://www.youtube.com/watch?v=7CqXdSEN2k4
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |