In Part I of this series, I demonstrated how to use Range Objects, Arrays and VBA to Filter and Delete data from a table. Today, I’ll use Power Query to Filter data from Table A based on criteria in Table B.

Load Data

I’ll begin by loading data from an Excel Workbook to Power Query in Excel. Here is the data I created in Part I of the series where I grabbed NFL winning percentages for all 32 NFL Teams. I replicated the data 9 more times giving me a total of 320 records.

Just like in Part I, I want to filter out the 4 teams in the NFC East. So, if everything works correctly, I should have 280 records when I am done.

Load Data Into Power Query

In Excel, click on the data menu (1). In the Get & Transform Data Group A(2), click on the Get Data icon (3).

In the pop-up menu, click on From File (4). From the next pop-up menu, select your file type. I’m using an Excel Workbook as my data source, so I’ll select Excel Workbook.

From the Import Dialog Window, navigate to the file that has your data, highlight the file (6) and click on the import button (7).

In the Navigator Dialog Window, tick the box next to “Select Multiple Items” (8). Next, tick the box next to the name of each worksheet you wish to import into Power Query (9). In this instance, there are 2 worksheets and I want to import both worksheets. Lastly, click on the Transform Data Button (10).

The data is now loaded into Power Query. There are two tables showing in the queries pane. The table with all records is named “Data” (Table A) and the table with the 4 teams I wish to filter out of the data are in the table named “crit” (Table B).

Now that I have all data that I need loaded to Power Query, I want to perform any transformations on the data and/or tables that are needed before I can use Table B as a filter on Table A.

Transformations

Power Query added a few transformations when I imported the worksheets into Power Query. The other transformation I need, is to convert the criteria table (Table B) into a List.

  1. Click on the crit Table in the Queries Pane
  2. Click on the Transform menu across the top of the Power Query Window
  3. Click on the Convert To List icon in the Any Column Group

The crit table (4) has been converted to a list (5). Note the different icon in the queries panel.

Filter Manually

Let’s take a look at what happens when I deselect the 4 teams that I want to remove from the filter drop-down in the Data Table. in the Text Filters drop-down (1), I removed the tick marks for the 4 NFC East teams (2)

A new Applied Step is added to the Applied Steps Pane (3). There are now 280 records (4) which is what I was after.

All well and good, however I want to use the list as the list of items to exclude, I don’t want to manually deselect teams. It is easy enough for this example, but imagine if I have a much larger table with many more items I need to exclude. Let’s take a look at the expression created when I manually deselected the 4 teams:

= Table.SelectRows(#"Changed Type", each ([Team] <> "Dallas Cowboys" and [Team] <> "New York Giants" and [Team] <> "Philadelphia Eagles" and [Team] <> "Washington Redskins"))

The expression is telling Power Query to return all Rows except those where the team is Dallas Cowboys, New York Giants, Philadelphia Eagles, or Washington Redskins. Those are the 4 teams I have listed on my criteria list “crit” so I need to replace

"Dallas Cowboys" and [Team] <> "New York Giants" and [Team] <> "Philadelphia Eagles" and [Team] <> "Washington Redskins"

with

List Contains

Also note in the expression, that Power Query is using the Applied Step Name #”Changed Type” in the place of the Data Table, so I will want to use the same Applied Step in my revised expression:

= Table.SelectRows(#"Changed Type", each List.Contains(crit,[Team])=false)

That is much easier to read. Note that I used =false to exclude the teams from the crit list. Here is the data table with the revised expression showing the 280 records as expected.

List Changes – Data Updates

The idea is to pass a dynamic list to filter the data table so I’ll check to see what happens to the data table when the list changes. I added the Cincinnati Bengals to the data source, saved and closed the workbook.

I returned to the Power Query Editor and refreshed all to update the list and the table. I added a fifth team so the list should return the 5 teams and the record count in the data table should now be 270.

I now have a List (Table B) that is filtering a Table (Table A). The expression now refers to a a list so that the filter is dynamic – as the data source expands and/or contracts, I can refresh all and the Data table will show more or fewer records depending on the teams listed in the data source.

Power BI Desktop

The steps are the same if you are using Power BI Desktop.

Tidy Up

That’s it for today. Grab both files from my OneDrive

Stay tuned for Part 3 in the series.

, , , , , , , ,