Forum Discussion
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 example of Table.Contains being used, or figure out how to pass it data that it will accept.
I can group by the Key, and do a min on the Step to get the correct unique identifier for the row I want, but then how do I include the other values associated with that minimum row? I have a work around with a merge inner join, but I don't want to actually merge the data, just use it as a filter.
How do I use Table.Contains with each as a filter for Table.SelectRows? I've uploaded a sample Power BI file to make it easier to look into.
My Data looks like:
Key Step Val
X 1 A
X 2 B
X 3 C
Y 2 J
Y 4 F
Y 6 K
Z 7 V
Z 8 C
Z 1 T
and the result I'm looking for is:
Key Step Val
X 1 A
Y 2 J
Z 1 T
The code I have so far:
// Table
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}})
in
#"Changed Type"
// Ft
let
Source = Table,
Custom1 = Table.SelectRows(Source, each List.Contains(#"Gp"[Key],[Key]) and List.Contains(#"Gp"[Step],[Step]))
in
Custom1
// Gp
let
Source = Table,
#"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Step", each List.Min([Step]), type number}})
in
#"Grouped Rows"
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 Custom1To 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.
6 Replies
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"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.
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.
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.
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.
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 Custom1To 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.
