Filtering Data by Criteria

Why Filtering is a Manager's Best Friend

Before diving into the "how," let's establish the "why" for your workshop audience. Filtering is not just a technical step; it's the primary way managers can start a conversation with their data. Explain to them that filtering is how they can answer questions like:

• "Show me all sales transactions from our top three performing regions."
• "I only want to see customer complaints that were submitted in the last quarter."
• "Let's analyze inventory levels for products with a stock count below 50 units."
• "Exclude all the test entries from this project log before we calculate the final metrics."

Without filtering, managers are forced to look at all their data at once, making it nearly impossible to spot specific trends or problems.

The Power Query Filtering Toolbox

Every column header in Power Query has a small downward-pointing arrow. This is the gateway to all filtering options. When you click it, you'll find a menu that provides several ways to filter your data.

To get a feel for the main filtering options, the official Microsoft documentation provides an excellent overview.

As the article notes, the main filtering methods are:

1. The Auto Filter List: Manually checking boxes next to the values you want to keep.
2. Type-Specific Filters: Using rules like "begins with" for text or "is greater than" for numbers.
3. The Cell Shortcut Menu: Right-clicking a cell to quickly filter by its value.

Let's explore these in more detail.

Method 1: The Auto Filter List (Simple Selection)

This is the most straightforward method, ideal for columns with a small number of unique values (e.g., Sales Region, Product Category, Status).

You can simply uncheck "(Select All)" and then tick the specific items you're interested in. While easy, this method can be tedious if the list is long.

Method 2: Type-Specific Filters (Rule-Based Filtering)

This is where the real power of Power Query shines. Power Query is intelligent; it recognizes the data type in a column (Text, Number, Date) and offers filtering rules appropriate for that type.

This video from the TrumpExcel channel provides a great walk-through of these contextual filters.

Let's break down the most common types:

• Text Filters: Let you filter based on patterns in the text. Common options include Contains, Begins With, and Does Not Equal. This is perfect for finding all products containing the word "Premium" or all customers whose names start with "A".
• Number Filters: Essential for quantitative analysis. You can filter for values that are Greater Than, Less Than, or Between two numbers. This is how you'd find all sales orders over $500.
• Date/Time Filters: Incredibly useful for business reporting. You can filter for dates Before or After a specific date, or use dynamic filters like In the Previous [month], This Year, or Year-to-Date. These dynamic options mean your report will automatically update over time without you needing to change the filter.

Combining Criteria: Basic and Advanced Mode

Often, a single filter isn't enough. You might need to combine several conditions. Power Query’s Filter Rows dialog box helps you do this.

This is clearly explained in the Microsoft documentation and demonstrated in the video you just watched.

To summarize:

• Basic Mode: For applying up to two conditions to the same column, connected by And or Or.
  • Example: Keep rows where Product contains "Laptop" OR Product contains "Tablet".
• Advanced Mode: For applying conditions across multiple columns.
  • Example: Keep rows where Region equals "North" AND Total Sale is greater than 1000.

An Advanced Technique: Dynamic Filtering with Lists

For your workshop, the methods above will cover 95% of use cases. However, as an instructor, you should be aware of a more advanced and highly efficient technique: filtering a large data table based on a separate list of criteria.

Imagine a manager wants to filter a sales table for 15 specific product codes. Manually checking 15 boxes in the filter list is tedious and prone to error. A better way is to put those 15 product codes in a separate Excel table, load it into Power Query, and then use a Merge Query as an Inner Join. This tells Power Query: "Keep only the rows in my main sales table that have a match in my list of 15 product codes."

This method is powerful because the manager can simply add or remove codes from the Excel list and refresh the query—no need to edit the Power Query steps at all!

Conclusion

You now have a complete toolkit for teaching managers how to filter data in Power Query. Filtering is a foundational skill that transforms a massive, overwhelming dataset into a focused, relevant, and actionable source of information.

Key Takeaways for Your Workshop:

• Filtering is about asking specific questions of your data to isolate what's relevant.
• Power Query offers data-type-specific filters (Text, Number, Date) that make it easy to apply logical rules.
• The Advanced Filter mode is used to combine criteria across multiple columns using AND / OR logic.
• All filter steps are recorded and automated, so they run again every time the data is refreshed.

Preview of the Next Lesson:
We've removed duplicate rows and filtered for relevant rows. But what about the cells within those rows that are empty? Missing data can break calculations and skew analysis. In our next lesson, we will tackle how to handle missing values by removing or replacing them.