Forum Discussion
Filter table with another table in Power Query
- Anonymous6 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 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.
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.