Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
4 years ago

Max Value per Order based on two columns

Hi experts!

 

I have an order table that shows me several rows per Order:

Order NrColumn 1Column 2Value
100213358300050
100213359000045
1012133205
1022133005

 

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?

Need Help

2 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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!

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi joshua1990 

     

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

     

    Best Regards,
    Community Support Team _ Jing