Power Query - Filter by specific individual Key per row

He experts! I'm working in Power Query at reducing the amount of data that is loaded into the model later. I have a big transactional table that contains multiple rows per Order:

The objective is to get the MAX Value (Last) column based on this filter for each Order:

As we can see for instance, the MAX Value for with the Type 1 = A1 is based on Key 1 = 50 and Key 2 = 20, regardless of Type 2. And so on.

Is there any chance to get this done within Power Query? The result would be like that:

Solution 1:

This is more complicated because of the wildcard

If you set up your Rules table as shown here (and name the query Rules)

then this code should work

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 
        let r = _ in Table.MatchesAnyRows(
            Rules,
            each List.Contains({r[Type 1], "*"}, [Type 1])
            and List.Contains({r[Type 2], "*"}, [Type 2])
            and List.Contains({r[Key 1], "*"}, [Key 1])
            and List.Contains({r[Key 2], "*"}, [Key 2])
    )),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Order"}, {{"Value", each List.Max([Value]), type number}})
in #"Grouped Rows"

this is the intermediate step being created before the group and max

Solution 2:

Assuming that a discrete Type 1 takes priority over a "wild card" Type 1, you might be able to deal with by doing .FuzzyNestedJoin, although, depending on your actual data, you might have to adjust the threshold from the default of 0.8

Also, depending on your actual data, you might want to change some of the data types (integer vs decimal number, etc).

Then

• Group by Order
• Create a filter using a comparison of the Type and Key columns
• Return the MAX of the Value column

let
    Source = Excel.CurrentWorkbook(){[Name="Filter"]}[Content],
    filter = Table.TransformColumnTypes(Source,{
        {"Type 1", type text}, {"Type 2", type text}, {"Key 1", Int64.Type}, {"Key 2", Int64.Type}}),
    
    Source2 = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    transactions = Table.TransformColumnTypes(Source2,{
        {"Order", Int64.Type},
        {"Type 1", type text},
        {"Type 2", type text},
        {"Key 1", Int64.Type},
        {"Key 2", Int64.Type},
        {"Value", Int64.Type}
    }),

    join = Table.FuzzyNestedJoin(transactions,"Type 1", filter,"Type 1","joined",JoinKind.LeftOuter),
    #"Expanded joined" = Table.ExpandTableColumn(join, "joined", 
        {"Type 1", "Type 2", "Key 1", "Key 2"}, {"joined.Type 1", "joined.Type 2", "joined.Key 1", "joined.Key 2"}),

    #"Grouped Rows" = Table.Group(#"Expanded joined", {"Order"}, {
        {"Value", (t)=> List.Max(Table.SelectRows(t, each (([joined.Type 2]=null) or ([Type 2]=[joined.Type 2])) and 
                [Key 1]=[joined.Key 1] and [Key 2]=[joined.Key 2])[Value]), Int64.Type}
        })
in
    #"Grouped Rows"