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. 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

This M code works

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"}, {{"Min", each List.Min([Step]), type number}}),
    Joined = Table.Join(Source, "Key", #"Grouped Rows", "Key"),
    #"Added Custom" = Table.AddColumn(Joined, "Test", each [Step]=Number.ToText([Min])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Test] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Min", "Test"})
in
    #"Removed Columns"
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Ashish_Mathur I'd like to avoid doing a join and then deleting the result if I can. There are many examples of filtering like this using Table.SelectRows and List.Contains, I'd just like to see an example with Table.Contains as this seems like the perfect solution if I could figure out the syntax.