Forum Discussion

israabuhasna's avatar
israabuhasna
Helper I
6 years ago
Solved

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?

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      israabuhasna
      Helper I
      6 years ago

      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.