Learn how to filter and sort rows in Power Query with genuine control — not just clicking dropdowns, but understanding the M code behind each step. This lesson uses a realistic sales dataset to teach text filters, date ranges, number conditions, multi-column sorting, and troubleshooting the mistakes that trip up beginners.
Imagine you've just connected Power Query to your company's sales database. The query pulls in 180,000 rows covering every transaction from the past five years across every region. Your manager needs a report on North American sales from the last 12 months only — and she needs it by end of day. You can't hand her a 180,000-row spreadsheet and say "good luck." You need to slice that data down to exactly the rows that matter, in an order that makes sense.
That's the job of filtering and sorting. These two operations are the bread and butter of data preparation — the first thing you reach for when raw data arrives in all its overwhelming, unordered glory. In Power Query, filtering and sorting are not just quick conveniences; they are documented, repeatable steps built into your query that re-run automatically every time your data refreshes. That means you set them up once, and the right rows come through every single time.
By the end of this lesson, you will be able to filter rows using basic conditions, multiple criteria, and text or date-specific logic. You will also be able to sort your data by one or more columns in any order you choose. More importantly, you will understand why Power Query handles these operations the way it does — so you can troubleshoot them when something unexpected happens.
What you'll learn:
Before working through this lesson, you should be comfortable with:
If any of those feel shaky, work through the earlier lessons in the Power Query Essentials path first.
In a spreadsheet, filtering is cosmetic. You click the little dropdown arrow on a column, uncheck some boxes, and certain rows appear to vanish. But they haven't gone anywhere — they're hidden. The moment you clear the filter, they're back.
Power Query is fundamentally different. When you filter rows in Power Query, you are adding a transformation step that removes those rows from the data flowing through your query. The rows don't get hidden — they get excluded. They don't exist downstream of that step.
This distinction matters a lot. It means:
Think of your query as a pipeline. Raw data flows in at the top. Each step is a gate that transforms, shapes, or restricts what passes through. Filtering is the gate that says "only rows matching this condition get through."
Let's work with a concrete scenario throughout this lesson. Suppose you have a sales transactions table with these columns:
| Column | Data Type | Example Value |
|---|---|---|
| TransactionID | Whole Number | 10042 |
| SaleDate | Date | 2024-03-15 |
| Region | Text | North America |
| Country | Text | United States |
| Product | Text | Enterprise License |
| Units | Whole Number | 12 |
| Revenue | Decimal Number | 48500.00 |
| Status | Text | Closed |
Your goal: filter this down to Closed deals in the North America region from 2024 onward, then sort by Revenue descending so the biggest deals are at the top.
Start by loading your table into Power Query. In Excel, click inside the table, go to the Data tab, and select From Table/Range. Power Query Editor opens and you'll see your data in the preview pane.
Now let's filter the Status column to show only rows where Status equals "Closed."
Click the small dropdown arrow in the Status column header. You'll see a list of all unique values that Power Query detected in that column. Uncheck everything except Closed and click OK.
A new step appears in the Applied Steps pane called Filtered Rows. Look at the formula bar at the top of the editor. You'll see something like this:
= Table.SelectRows(#"Changed Type", each [Status] = "Closed")
Let's break this down because understanding this line will unlock a lot of power later:
Table.SelectRows is the M function that does the filtering. It takes a table and a condition, and returns only the rows where the condition is true.#"Changed Type" is the name of the previous step — the one this step is pulling from.each [Status] = "Closed" is the condition. The keyword each means "for every row," and [Status] means "the value in the Status column for that row."In plain English: For every row in the table from the previous step, keep it only if its Status column equals "Closed."
Tip: If your dropdown shows only a partial list of values (Power Query samples a limited number of rows), you can type a value manually rather than selecting from the list. This is covered in the next section.
Exact match works great when your values are clean and consistent. But real data is messy. What if you want all products that contain the word "License"? That's where Text Filters come in.
Click the dropdown arrow on the Product column. Instead of unchecking values, hover over Text Filters in the dropdown menu. A submenu appears with options like:
Choose Contains, then type License in the dialog box that appears and click OK.
The M code this generates:
= Table.SelectRows(#"Filtered Rows", each Text.Contains([Product], "License"))
Text.Contains is a built-in M function that returns true if the second argument appears anywhere in the first. Note that this is case-sensitive by default. If your data has "license" in lowercase, this filter would miss it.
Warning: Power Query text operations are case-sensitive unless you explicitly handle casing. A common mistake is filtering for "North America" and missing rows that say "north america" or "NORTH AMERICA." One defensive approach: add a step before the filter that standardizes case (using Transform → Format → UPPERCASE or lowercase), then filter against the standardized version.
What if you want rows from both the North America and Europe regions? You have two clean options.
Option 1: Checkbox selection in the dropdown
Click the dropdown on the Region column and check both North America and Europe. Power Query writes this as an or condition:
= Table.SelectRows(#"Filtered Rows2", each [Region] = "North America" or [Region] = "Europe")
Option 2: Use the Custom Filter dialog Click the dropdown on Region, hover over Text Filters, and choose Equals. In the Filter Rows dialog, you can set two conditions and choose whether they should be connected by And or Or. For two acceptable values, choose Or, set the first condition to equals "North America" and the second to equals "Europe."
The difference between And and Or is critical:
A very common beginner mistake is using And when Or is needed. If you write [Region] = "North America" and [Region] = "Europe", no row can ever satisfy that — a single cell can't contain two different values at the same time. You'd get zero results.
Now let's filter the Revenue column to only include deals worth more than $10,000.
Click the dropdown on Revenue, hover over Number Filters, and choose Greater Than. Enter 10000 and click OK.
The generated M code:
= Table.SelectRows(#"Filtered Rows3", each [Revenue] > 10000)
Number filters include the full set of comparisons you'd expect: greater than, less than, between (which creates an AND condition), equals, and so on.
Between is particularly useful. If you only want mid-market deals between $10,000 and $100,000:
= Table.SelectRows(#"Filtered Rows3", each [Revenue] >= 10000 and [Revenue] <= 100000)
Date filtering deserves its own section because it has some behavior that trips people up.
Click the dropdown on SaleDate. You'll notice Power Query offers a hierarchical tree of values — years expand to months, months expand to days. You can check entire years or drill down to specific months.
But for dynamic, ongoing reports, hardcoding specific dates is fragile. If you filter to the year 2024 today, that filter still says 2024 a year from now. What you usually want is something like "the last 12 months" or "this year."
Hover over Date Filters in the SaleDate dropdown. You'll find options like:
For our goal of filtering to 2024 and beyond, choose Is After or Equal To and enter 1/1/2024.
The M code:
= Table.SelectRows(#"Filtered Rows4", each [SaleDate] >= #date(2024, 1, 1))
#date(2024, 1, 1) is Power Query's way of writing a date literal. You'll see this notation in the formula bar.
Tip: For truly dynamic date filtering (e.g., "always show the last 90 days"), you can use
DateTime.LocalNow()in the formula. For example:each [SaleDate] >= Date.From(DateTime.LocalNow()) - #duration(90, 0, 0, 0). This is a bit advanced for this lesson, but it's worth knowing it exists.
With your rows filtered down to the right set, now let's put them in a meaningful order.
To sort Revenue from highest to lowest (descending), click the dropdown arrow on the Revenue column header and choose Sort Descending. Or right-click the column header and choose Sort Descending from the context menu.
A new step appears in Applied Steps called Sorted Rows. The formula bar shows:
= Table.Sort(#"Filtered Rows4", {{"Revenue", Order.Descending}})
Table.Sort takes a table and a list of sort instructions. Each instruction is a pair: the column name and the sort direction (Order.Ascending or Order.Descending).
To sort ascending (smallest to largest, or A to Z for text), choose Sort Ascending or use Order.Ascending in the code.
Here's where people get confused. If you sort by Region, then sort by Revenue, Power Query creates two separate sort steps. The second sort completely replaces the first. You end up sorted only by Revenue.
To sort by multiple columns simultaneously — say, first by Region alphabetically, then by Revenue descending within each region — you need to do it in one step.
The cleanest way is to edit the M code directly. Click on the Sorted Rows step to select it, then click in the formula bar and modify it to list both sort criteria:
= Table.Sort(#"Filtered Rows4", {{"Region", Order.Ascending}, {"Revenue", Order.Descending}})
This tells Power Query: first sort by Region A to Z, and within each region, sort by Revenue from highest to lowest.
Alternatively, you can hold Shift while clicking sort arrows on columns — Power Query will sometimes recognize a multi-column sort from the UI, but editing the code directly is more reliable and explicit.
Tip: The order of items inside the curly braces matters. Power Query applies the sorts left to right. The first item is the primary sort, the second is the secondary sort (tie-breaker), and so on.
At this point you've built up several steps. Your Applied Steps pane might look like:
Each step is a checkpoint you can click on to see the data at that exact point in the pipeline. This is enormously useful for debugging — if your final output looks wrong, click backward through the steps to find where the data goes sideways.
To edit a filter, click the gear icon (⚙️) next to the step name. The original dialog reopens and you can change your criteria.
To delete a step, click the X next to it. Be careful: deleting a step in the middle of the chain can break later steps that depend on it.
Work through this exercise from start to finish to solidify what you've learned.
Setup: Create an Excel table with at least 20 rows containing these columns: EmployeeID, Department, HireDate, Salary, Status. Populate it with mixed data — a few departments (Engineering, Sales, HR), various hire dates spanning 2019–2024, salaries ranging from $40,000 to $150,000, and statuses of Active or Inactive.
Your task:
Check your work: After loading, verify that:
"My filter removed more rows than expected."
Check for data type mismatches. If a column looks like numbers but Power Query classified it as Text, filtering [Salary] > 60000 on a text column won't work correctly. Look at the column header icon — it shows the data type (ABC for text, 123 for whole number, etc.). Fix the type before filtering.
"I filter for 'North America' but get zero results." Almost always a case-sensitivity or whitespace issue. Try filtering using Contains instead of Equals to test if the value appears at all. Then inspect the raw values by clicking into a cell. Invisible leading/trailing spaces are a very common culprit — use Transform → Format → Trim to clean the column before filtering.
"My second sort wiped out my first sort."
This happens when you apply sorts one at a time through the UI. Each sort creates a new step that doesn't know about the previous one. Combine them into a single Table.Sort call with multiple criteria in the list, as shown above.
"After filtering, my row count looks wrong on refresh." Your filter is likely hardcoded to values that existed when you set it up. If you filtered by selecting checkboxes from the dropdown, Power Query recorded the specific values it saw. New values that appear in refreshed data won't be included (or excluded) unless you update the filter. Review the M code and make sure your condition is written as a logical expression, not just a list of hardcoded values.
"The Applied Steps show 'Filtered Rows', 'Filtered Rows1', 'Filtered Rows2' — that's confusing." Right-click any step and choose Rename to give it a meaningful name like "Filter Active Only" or "Filter Date Range." This is a good habit that makes your query readable and maintainable.
Here's what you've built up in this lesson:
Table.Sort can handle multiple columns when you provide a list of sort criteria — and editing the M code directly is the most reliable way to do this.Understanding filtering and sorting means you're thinking like a data engineer: you're building a pipeline, not clicking around in a spreadsheet. Every decision you make is documented, reproducible, and automatic on refresh.
Where to go next:
Table.GroupThe more comfortable you get with these foundational operations, the faster and more confidently you'll tackle complex data preparation tasks. Keep building.