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

[israabuhasna]

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:

Source	BuildingID	City
S1	1	London
S2	2	Chicago
S3	3	Denver
S2	4	Cleveland
S2	5	Springfield
S1	6	Jackson

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

BuildingID	State
2	IL
4	OH

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?

[Anonymous]

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]

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.

[v-piga-msft · 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.

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

Best  Regards,

Cherry

[HotChilli]

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)