cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
israabuhasna Frequent Visitor
Frequent Visitor

Filtering table based on another table conditions in Power Query without merging tables

Hi,

 

I'm trying to filter a table based on another table column in Power Query. I do not want to merge the two tables to create a whole new table. Example: I have two separate tables, 1 with the list of sources and BuildingID and City:

SourceBuildingID City
S11London
S22Chicago
S33Denver
S24Cleveland
S25Springfield
S16Jackson

Second table is the Source 'S2' table with the BuildingID as the key column and the State:

BuildingIDState
2IL
4OH

 

I would like to filter table 1 based on if the Source 'S2' table BuildingIDs column. We can see that Table S2 does not contain ID '5' so i would like it filtered out of table 1. I tried different formulas but to no avail. Last formula I attempted:

'#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", if [SOURCE] = "S2" then each List.Contains(#"SiteSolutions T_BUILDINGS"[BUILDING_KEY],[BUILDING_KEY]) else each true)'

I got this error:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filtering table based on another table conditions in Power Query without merging tables

Hi @israabuhasna ,

By my tests and research, you could try the power query below.

Table.SelectRows(#"Changed Type", each List.Contains(#"Table 2"[BuildingID],[BuildingID]))

Here is the output.

Untitled.png

In addition, you also could download my test file and do a research.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
MrMister Visitor
Visitor

Re: Filtering table based on another table conditions in Power Query without merging tables

Hi,

 

maybe this works without the if-condition:

 

Table.SelectRows(#"Filtered Rows1", each List.Contains(#"SiteSolutions T_BUILDINGS"[BUILDING_KEY],[BUILDING_KEY]))

 

I am trying to figure out this filtering technique as well for some stats at work.

 

My goal is to load a query based on the results from a pre-loaded first query's column.

 

Best

israabuhasna Frequent Visitor
Frequent Visitor

Re: Filtering table based on another table conditions in Power Query without merging tables

No it won't work because some sources have the same BuildingID and I need to filter only for the source 'S2' since Table2 is only for source S2. Thank you for the response though.

HotChilli New Contributor
New Contributor

Re: Filtering table based on another table conditions in Power Query without merging tables

I think this version works.  Please test with your data and field names

 Table.SelectRows(#"Changed Type", each if [Source] = "S2" then  List.Contains(#"Table 2"[BuildingID], [BuildingID]) else true)
Community Support Team
Community Support Team

Re: Filtering table based on another table conditions in Power Query without merging tables

Hi @israabuhasna ,

By my tests and research, you could try the power query below.

Table.SelectRows(#"Changed Type", each List.Contains(#"Table 2"[BuildingID],[BuildingID]))

Here is the output.

Untitled.png

In addition, you also could download my test file and do a research.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 265 members 2,321 guests
Please welcome our newest community members: