How to add dynamic filter in Power Query

1
votes

I want to filter one table by using a column in another table. Here is my table to be filtered:

Table 1

deviceid, user_number  
001,400
002,500
003,600
004,700
009,1000

I have another table:

Table 2

deviceid
001
003
004

Now I can filter table 1 with table 2 by writing something like this: 

"Filtered Rowxx" = Table.SelectRows(#"Column1", each ([deviceid] <> "001" 
and [deviceid] <> "003" and [deviceid] <> "004")

Since Table 2 changes all the time, how can I define a query under power query to achieve this please?

2
excelfilterpowerquery

2 Answers

1
votes

It sounds like you want an anti-join.

Table.Join with JoinKind.LeftAnti will filter out rows from the left table that match the right table. 

let
    Column1= #table({"deviceid", "user_number"}, { {"001", "400"}, {"002", "500"}, {"003", "600"}, {"004", "700"}, {"009", "1000"} }),
    Table2 = #table({"__deviceid"}, { {"001"}, {"003"}, {"004"} }),

    AntiJoin = Table.Join(#"Column1", "deviceid", Table2, "__deviceid", JoinKind.LeftAnti),
    SelectColumns = Table.SelectColumns(AntiJoin, {"user_number", "deviceid"})
in
    SelectColumns

One benefit of Table.Join is it's likely to fold your filter to a backing relational database server, while list operations tend not to fold. (But if your query runs quickly, it doesn't really matter.)

One problem with Table.Join is it can't handle multiple columns with the same name, so I had to rename the Table2's "deviceid" column.

0
votes

You can get the column deviceid of Table2 by using Table2[deviceid], which gives you a list. We can check if the current deviceid is in the list with List.Contains, so your filter can look something like this:

"Filtered Rowxx" = Table.SelectRows(#"Column1", each not List.Contains(Table2[deviceid], [deviceid]))