Knowledge base

For Power BI version 2.76.5678.782 (December 2019).

In shaping data, we often need to compare two tables and filter one based on the other. In this article we’ll show how to remove the rows from a table, that appear in another table.

Let’s look at the following example: we want to filter Sales table, so that we’re left with all genres in Genre field, except for genres Party, Platform in Puzzle. We use the following steps.

1. Open Power Query with Transform Data.

2. Select the filter in Genre field and check Select All. Uncheck genres Party, Platform in Puzzle. Confirm with OK.

3. Select and open Advanced Editor. We see the step saved as

#”Filtered Rows” = Table.SelectRows(#”Renamed Columns1″,

each ([Genre] <> “Party”

and [Genre] <> “Platform”

and [Genre] <> “Puzzle”))

4. We’ll edit the step to take the list as an argument instead of elements Party, Platform in Puzzle.

5. We’ll enter data we want to filter into a table and convert the table into a list. To create the table select Enter Data and type in values Party, Platform in Puzzle. We name the table GenreList. Confirm with OK.

6. Convert the table to a list by selecting Transform > Convert to List.

7. We don’t need to create table manually, we could use a table from any other queries.

8. Edit the formula in the filtering step of the query. The formula is now:

#”Filtered Rows” = Table.SelectRows(#”Renamed Columns1″,

each List.Contains(GenreList, [Genre]) = false)

Confirm with OK.

This step will now check if the element is found on the GenreList for every element of the table. After filtering we’ll be only left with elements that are not on the list.