CODEWITHSUNDEEP
powerquery
Aaron
Aaron

Reputation: 331

Power Query - Max Row based on two Numbers

I have the following table:

Order Nr    Number 1    Number 2    Value
100 250 66  100
100 251 67  100
100 252 65  80
100 252 64  60
100 252 63  703
101 250 80  100
101 251 81  100
101 252 85  80
101 252 84  60
101 252 83  703

Now I would like to use Power Query to get just 1 row per Order based on the Max of Number 1 and then based on Number 2. For instance Order Nr '100'. The Max of Number 1 is 252. And the Max in this range in Number 2 ist 65. Therefore the row with Value 80 should be the result. How would you do that in Power Query?

Upvotes: 0

Views: 1989

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

You can do this within the Table.Group command.

  • Group by order
  • then sequential filters to return the desired values:

***edited because I had table headers incorrect

let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Order Nr", Int64.Type}, {"Number 1", Int64.Type}, {"Number 2", Int64.Type}, {"Value", Int64.Type}}),

//Group by "Order Nr"
//Then extract Maximum Number 1 and sequential filters to get the associated  values
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Nr"}, {
        {"Number 1", each List.Max([Number 1]), type nullable number}, 
        {"Result", (t)=> let 
                        filter1 = Table.SelectRows(t, each [Number 1] = List.Max(t[Number 1])),
                        filter2 = Table.SelectRows(filter1, each [Number 2] = List.Max(filter1[Number 2]))
                            in 
                        filter2}}),
    #"Expanded Result" = Table.ExpandTableColumn(#"Grouped Rows", "Result", {"Number 2", "Value"}, {"Number 2", "Value"})
in
    #"Expanded Result"

enter image description here

Upvotes: 2

smpa01
smpa01

Reputation: 4282

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Nr", Int64.Type}, {"Number 1", Int64.Type}, {"Number 2", Int64.Type}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Nr"}, {{"_1", each List.Max([Number 1]), type nullable number}, {"_2", each List.Max([Number 2]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Order Nr", "_1", "_2"}, #"Changed Type", {"Order Nr", "Number 1", "Number 2"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Value"}, {"Value"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Grouped Rows",{{"Order Nr", Order.Ascending}})
in
    #"Sorted Rows"

SOlution

Upvotes: 0

horseyride
horseyride

Reputation: 21318

Group on Order number, return All Rows and Max of Number 1 column. Expand

Group on Order number and Number 1, return All Rows and Max of Number 2 column. Expand

Add custom column with formula as below, then filter

=if [Number 1]=[Number1Max] and [Number 2]=[Number2Max] then "keep" else "remove")

full sample code

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Order Nr"}, {{"Number1Max", each List.Max([Number 1]), type number}, {"Data", each _, type table}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Number 1", "Number 2", "Value"}, {"Number 1", "Number 2", "Value"}),
#"Grouped Rows1" = Table.Group(#"Expanded Data", {"Order Nr", "Number 1"}, {{"Number2Max", each List.Max([Number 2]), type number}, {"Data", each _, type table}}),
#"Expanded Data1" = Table.ExpandTableColumn(#"Grouped Rows1", "Data", {"Number1Max", "Number 2", "Value"}, {"Number1Max", "Number 2", "Value"}),
#"Added Custom" = Table.AddColumn(#"Expanded Data1", "Custom", each if [Number 1]=[Number1Max] and [Number 2]=[Number2Max] then "keep" else "remove"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "keep")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Number2Max", "Number1Max", "Custom"})
in #"Removed Columns"

enter image description here

Upvotes: 1

Related Questions