Dynamic Filter by a Range of Values in Power Query

Summary

Chandep Goodly demonstrates how to use a user-editable range of values to dynamically filter data in Power Query.

The example includes a data table with Category and Value columns, along with a separate one-column table listing the categories to include. In Power Query, the filter table’s column is converted to a list using Drill Down.

To filter the data, the tutorial replaces a hard-coded category filter with List.Contains. This function checks whether each row’s Category value appears in the filter list and keeps matching rows.

As an alternative, the tutorial adds a custom column with the same List.Contains check in an if/then/else expression. Matching categories are labeled “best product,” while the others are labeled “average product.”

After loading the results to Excel, the speaker changes the filter list—for example, replacing C with D and adding B—then refreshes the query. The output updates to reflect the revised list, demonstrating the dynamic behavior.

Main speaker/source: Chandep Goodly, presenting a Power Query tutorial.