CODEWITHSUNDEEP
excelpowerbipowerquery
Aaron
Aaron

Reputation: 331

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: enter image description here

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

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:

enter image description here

Upvotes: 0

Views: 1037

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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"

enter image description here

Upvotes: 1

horseyride
horseyride

Reputation: 21298

This is more complicated because of the wildcard

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

enter image description here

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

enter image description here

Upvotes: 1

Related Questions