Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi experts!
I have an order table that shows me several rows per Order:
Order Nr | Column 1 | Column 2 | Value |
100 | 21335 | 83000 | 50 |
100 | 21335 | 90000 | 45 |
101 | 21332 | 0 | 5 |
102 | 21330 | 0 | 5 |
Using Power Query I would like to group/ pivot this table by Order Nr and Value. And the Value is based on the Max of Column 1 and Column 2.
How is that possible within PQ?
Hi @joshua1990
What's the expected output based on this sample data?
Best Regards,
Community Support Team _ Jing
Hi,
I am not sure if I understood correctly but here is a commented PQ that might be of use.
Start data:
PQ:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNKBkhbGBkqxOqhilnAxIyDPGCwGEzEGsk1gIrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t, C1 = _t, C2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Number", Int64.Type}, {"C1", Int64.Type}, {"C2", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Value", each List.Max({[C1],[C2]})), //Here we get the max of columns1 and 2
#"Grouped Rows" = Table.Group(#"Added Custom", {"Order Number"}, {{"Value", each List.Sum([Value]), type nullable number}}) //Here we group the values based on order
in
#"Grouped Rows"
I hope this helps and if it does consider accepting this as a solution!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.