Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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.
In addition, you also could download my test file and do a research.
Best Regards,
Cherry
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)