Filtering and Sorting Rows in Power Query: Practical Techniques for Slicing Your Data

Introduction

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:

• How the filter dropdown works in Power Query and what it's actually doing under the hood
• How to filter rows by text values, number ranges, and date ranges
• How to apply multiple filter conditions using AND and OR logic
• How to sort by one column or multiple columns at the same time
• How to read and edit the M code that filtering and sorting generate

---

Prerequisites

Before working through this lesson, you should be comfortable with:

• Opening Power Query Editor from Excel (via the Data tab → Get Data or Launch Power Query Editor)
• Basic familiarity with what a query is — a saved set of steps that transforms your data
• Loading a data source (like a CSV or Excel table) into Power Query

If any of those feel shaky, work through the earlier lessons in the Power Query Essentials path first.

---

What Filtering Actually Means in Power Query

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:

1. Every filter you apply is visible and editable in the Applied Steps pane on the right side of the editor.
2. The filter runs every time the query refreshes, so if new data comes in that fails your filter condition, it won't appear in your output.
3. You can go back and change a filter at any time by clicking the gear icon next to its step name.

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."

---

Setting Up: A Realistic Dataset

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.

---

Filtering by a Single Text Value

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.

---

Filtering with Text Filters: Contains, Begins With, and More

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:

• Equals — exact match
• Does Not Equal — exclude an exact match
• Begins With — value starts with your text
• Ends With — value ends with your text
• Contains — your text appears anywhere in the value
• Does Not Contain — your text does not appear

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.

---

Filtering by Multiple Conditions on the Same Column

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:

• And means both conditions must be true for a row to pass. Use this to narrow down further (e.g., Region is North America and Status is Closed).
• Or means either condition can be true. Use this to broaden your results (e.g., Region is North America or Region is Europe).

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.

---

Filtering by Number Ranges

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)

---

Filtering by Dates

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:

• Is In The Previous N Days/Months/Years — dynamically relative to today
• Is After — date is after a specific value
• Is Before — date is before a specific value
• Is Between — date is within a range

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.

---

Sorting Rows

With your rows filtered down to the right set, now let's put them in a meaningful order.

Sorting by a Single Column

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.

Sorting by Multiple Columns

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.

---

Reading and Editing the Applied Steps

At this point you've built up several steps. Your Applied Steps pane might look like:

1. Source
2. Navigation
3. Changed Type
4. Filtered Rows (Status = "Closed")
5. Filtered Rows1 (Region filter)
6. Filtered Rows2 (Revenue > 10000)
7. Filtered Rows3 (SaleDate >= 2024)
8. Sorted Rows

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.

---

Hands-On Exercise

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:

1. Load the table into Power Query.
2. Filter to only Active employees.
3. Filter to only employees in Engineering or Sales.
4. Filter to employees hired on or after January 1, 2021.
5. Filter to employees with a Salary between $60,000 and $120,000.
6. Sort by Department ascending, then by Salary descending within each department.
7. Close and load the query to a new sheet.

Check your work: After loading, verify that:

• No Inactive employees appear
• No HR employees appear
• No hire dates before 2021 appear
• No salaries outside the $60k–$120k range appear
• Rows are ordered correctly (Engineering before Sales, highest salaries first within each)

---

Common Mistakes & Troubleshooting

"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.

---

Summary & Next Steps

Here's what you've built up in this lesson:

• Filtering in Power Query removes rows permanently from the downstream pipeline — it's not hiding, it's excluding. That behavior makes your queries repeatable and trustworthy.
• The dropdown filter menus give you quick access to text filters, number filters, and date filters, each tailored to the data type.
• And logic narrows your results (both conditions must be true); Or logic broadens them (either condition can be true).
• Sorting with 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.
• The Applied Steps pane is your audit trail. Click through it to debug, rename steps to stay organized, and use the gear icon to edit existing filters.

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:

• Adding Custom Columns — learn to create calculated fields using M expressions so you can derive new values from existing columns before or after filtering
• Grouping and Aggregating Rows — once you have the right rows, learn to summarize them with sums, counts, and averages using Table.Group
• Combining Queries with Merge and Append — bring multiple filtered datasets together into a unified result

The more comfortable you get with these foundational operations, the faster and more confidently you'll tackle complex data preparation tasks. Keep building.