Max Value per Order based on two columns

[joshua1990]

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?

[v-jingzhang]

Hi @joshua1990 

What's the expected output based on this sample data?

Best Regards,
Community Support Team _ Jing

[ValtteriN]

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!