Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter table with another table in Power Query

I want to filter a table with another table based on multiple columns using Power Query. There are already many examples out there using Table.SelectRows and List.Contains, but I can't find an exampl...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I finally got a solution working with Table.Contains.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUTIEYkelWB0IzwiIneA8YyB2BvMioXJecJ4JELvBeWZA7A3mRQFZ5kAcBudZwE2JgtoXohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t, Step = _t, Val = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Step", Int64.Type}, {"Val", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Key"}, {{"Step", each List.Min([Step]), type number}}),
    Custom1 = Table.SelectRows(#"Changed Type", each Table.Contains(#"Grouped Rows",_,{"Key","Step"}))
in
    Custom1

     

    To get this working, the Table.Contains operates over the grouping step to get the set of keys I want to use as a filter. Then it operates over the _ , which is a record object that is available by running "each" as a comparer function. As my resulting table has more columns than the grouped result, I pass in a list, defined with {}, of the column names that should be used to compare them. If your group step had different column names, you would need to find a way to alias the fields found in the "_" record. 

kentyler's avatar
kentyler
Solution Sage
6 years ago

Just out of curiosity, where are the tables coming from. Sounds like it would be trivial to do in SQL Server, say, before loading the data.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Long term the goal is to probably move all of this to SQL, however there is a lot more logic that happens before we get to this step that would also need to be ported.

 

I've updated my post to include a sample file shared from OneDrive if that makes this easier. It includes the original table as defined in Power Query, and the expected result based on multiple filters passed in.

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    I took a look at your file. There are a couple of ideas that suggest themselves...but it's hard to tell from the sample tables.

    If you'd like to do a screen share and discuss this further send a good time and date and I'll send you a meeting invitation.