cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rahme
Regular Visitor

How to filter a column based on a column from another table?

I have Table 1 that contains a column of ALL of my employees:

Employee IDSales Value
125
250
375
4100
5100


I have Table 2 that contains a SUBSET of employees and their info:

Employee IDFirst NameLast Name
2JonDoe
4JaneDoe


I want to filter my Table 1, so that it only contains rows that show up in Table 2. Table 1 after the filter should look like this:

Employee IDSales Value
250
4100

 

How do I achieve this? Bonus: is there a way to achieve this without doing a Join? The tables are millions of rows long, so I don't know how join would impact performance.

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

Hi @rahme 

An inner join should do it and is quite probably the best performing option 

Place the following M code in a blank query to see the steps.

let
    Source = Table.NestedJoin(Table1, {"Employee ID"}, Table2, {"Employee ID"}, "Table2", JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Table2"})
in
    #"Removed Columns"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

2 REPLIES 2
Icey
Community Support
Community Support

Hi @rahme ,

 

You can also try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyVYrViVYyAjJNDcBMYyDTHCJqAmQaGkCETWHsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Sales Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Sales Value", Int64.Type}}),
    #"Filtered Table1" = Table.SelectRows(#"Changed Type", each List.Contains(#"Table2"[Employee ID],[Employee ID]))
in
    #"Filtered Table1"

employee id.JPG

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlB
Super User III
Super User III

Hi @rahme 

An inner join should do it and is quite probably the best performing option 

Place the following M code in a blank query to see the steps.

let
    Source = Table.NestedJoin(Table1, {"Employee ID"}, Table2, {"Employee ID"}, "Table2", JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Table2"})
in
    #"Removed Columns"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors