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
israabuhasna
Helper I
Helper I

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

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
Anonymous
Not applicable

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

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.

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.

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)

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.

Top Solution Authors
Top Kudoed Authors