Power Query Problems

How to Filter an Excel Dataset Using a List of Values in Power Query

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs a simple way to filter a 500,000-row Excel dataset using a list of 560 unique values.

Product: Microsoft Excel (Microsoft 365)
Device & OS: not provided
Scenario: The organization is migrating to a new server where their previous OLAP PivotTable Extensions add-in is no longer supported, requiring a simple native alternative for less experienced users.
Observed behavior: Users need an efficient, built-in method to apply a large list of filter values to a massive dataset without manually checking hundreds of boxes in the filter menu.

Before you start

Ensure both your main dataset and your list of filter values are formatted as Excel Tables (press Ctrl+T) before importing them into Power Query.

Solution 1Recommended

Use Power Query to Merge and Filter the Dataset

This method uses an Inner Join in Power Query to automatically filter your main dataset against the list of required values without loading all 500,000 rows into the standard worksheet.

Power Query is highly efficient for handling large datasets. By connecting the main data and the filter list using an inner join, Excel will only retain the rows that match your 560-item list.

This completely bypasses the need for unsupported third-party add-ins and saves less experienced users from manual data sorting.

Create a Table for your filter values

Convert your 560-value filter list into an Excel Table and name it 'FilterList' in the Table Design tab.

Load the filter list as a connection

Go to the Data tab, click 'From Table/Range' to load the filter list into Power Query. Once open, click 'Close & Load To...', select 'Only Create Connection', and click OK.

Import the main dataset

Connect to your 500,000-row main dataset by going to Data > Get Data (from file or database, depending on your source), and load it into the Power Query Editor.

Merge the queries

In the Power Query Editor with your main dataset selected, go to the Home tab and click 'Merge Queries'.

Configure the Inner Join

Select your main dataset as the first table and select 'FilterList' as the second table. Click the column headers in both tables that contain the matching values. At the bottom, change the Join Kind to 'Inner (only matching rows)' and click OK.

Load the filtered data

Remove any unnecessary columns added by the merge operation, then click 'Close & Load' on the Home tab to output the newly filtered dataset.

Performance Tip: Creating a connection instead of loading the full 500,000-row dataset into a worksheet saves memory and significantly speeds up processing in Microsoft 365.

Filter Large Data in WPS Office

How to Filter Datasets Using Advanced Filter in WPS Spreadsheet

WPS Spreadsheet provides a powerful built-in Advanced Filter tool that allows you to use a list of values as filtering criteria, completely bypassing the need for external add-ins.

1. Prepare your criteria: Place your 560-value filter list in a single column and ensure the top cell (the header) exactly matches the header of the column you want to filter in your main dataset.
2. Open the Advanced Filter tool: Go to the Data tab in the WPS Spreadsheet ribbon and click on 'Advanced' in the Filter section.
3. Set your data ranges: Select your large main dataset for the 'List range', and then highlight your 560-value list (including the header cell) for the 'Criteria range'.
4. Apply the filter: Choose whether to filter the list in place or copy the results to another location, then click OK to execute the filter.

Natively handles complex list-based filtering without third-party add-ins.Fully compatible with Microsoft Excel (.xlsx) file formats.Familiar user interface makes the transition seamless for Microsoft 365 users.Lightweight processing ensures smooth handling of large data tables.

Frequently Asked Questions

Can I filter a large dataset without using Power Query?

Yes, you can use the Advanced Filter feature found under the Data tab. By setting your main data as the List Range and your values as the Criteria Range, Excel will filter the data. However, for datasets approaching 500,000 rows, Power Query is generally more stable and memory-efficient.

Why is my OLAP PivotTable Extensions add-in no longer supported?

Add-in compatibility often depends on the specific server environment, changes in Microsoft 365 architecture, or updated IT security policies that restrict third-party extensions on new servers.

What is an Inner Join in Power Query?

An Inner Join is a specific type of data merge that compares two tables and only keeps the rows where there is a matching value in both tables. In this scenario, it acts as a powerful, automatic filter for your large dataset.

Will merging a 500,000-row dataset freeze my computer?

If you process the merge entirely within Power Query and ensure the raw data is only loaded as a 'Connection', the processing happens in the background. This minimizes RAM usage and typically prevents the software from freezing.

Excel & Data Problems