Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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"

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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. 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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
Super User
Super User

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"

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@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.

kentyler
Solution Sage
Solution Sage

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.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

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.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.