Filter Records Using Power Query or Get & Transform

Excel is a fantastic tool for managing, cleaning, and analyzing data. But if you’ve ever tried to filter your data with more complex conditions—especially using OR logic—you probably know it’s not always straightforward with Excel’s standard filtering tools. That’s where Power Query (known as Get & Transform in Excel 2016) comes in and saves the day. Power Query lets you perform a series of transformation steps to prepare your data exactly how you want it.

In this article, we’ll walk through how to filter records using Power Query in Excel.

Key Takeaways:

• Power Query is a built-in Excel tool that simplifies data transformation and cleaning.
• Filtering with OR logic is much easier in Power Query than with standard Excel filters.
• Power Query steps are repeatable and refreshable, saving time on recurring tasks.
• You can import data from multiple sources, transform it, and automate updates.
• Advanced filtering, including text and conditional logic, can be done without formulas.

 

 

Understanding Power Query and Its Advantages

What is Power Query?

Power Query is a built-in data transformation and preparation tool in Excel (also available in Power BI and other Microsoft products). Think of it as Excel’s data-cleaning powerhouse.

Instead of manually copying, pasting, and fixing data, Power Query lets you:

• Import data from multiple sources (Excel, CSV, databases, websites, etc.).
• Transform it (filter, merge, split, pivot, unpivot, clean text, remove duplicates, etc.).
• Automate the steps, so the next time your data updates, you just hit Refresh and all the cleanup happens instantly.

It works in the background using its own language called M code, but the best part is—you don’t need to know coding. Everything can be done with clicks, and Excel remembers each step for you.

Why Use Power Query for Filtering?

Filtering in regular Excel is fine when you just need to hide rows quickly. But if you want to combine multiple conditions (especially OR conditions), things get messy. You might have to write helper columns with formulas, use complex advanced filters, or manually apply multiple filters again and again.

With Power Query, filtering is:

• Repeatable – Once you set it up, the steps are saved.
• Flexible – You can use AND/OR logic, advanced text matching, and dynamic filters.
• Automated – New data? Just refresh, and your filtering rules are applied instantly.

And the coolest part? You don’t need to write formulas. Power Query gives you a friendly interface where you just click and choose your logic.

 

Steps to Filter Records using Power Query

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 

Excel 2013 & 2010:

STEP 3: This will open up the Power Query Editor.

Go to Filter Arrow of Full Name and select Text Filters > Begins With

Here is where the magic happens!  Select Advanced and make sure your conditions are:

Full Name begins with Y

Or Country equals USA

Click OK.

STEP 4: Click Close & Load from the Home tab, and this will open up a brand new worksheet in your Excel workbook with the updated table.

You now have your new table with the filtered rows kept!

 

Practical Use Cases and Tips

Real-World Applications of OR Filters

This technique isn’t just useful for toy datasets. You can apply OR filters in countless real-life scenarios:

• Keep sales records where Product = “Laptop” OR Region = “North”.
• Filter employee lists where Department = “HR” OR Age > 50.
• Select transactions where Amount > 1000 OR Payment Method = “Credit Card”.
• Extract survey results where Response = “Yes” OR Comments contain “urgent”.

Basically, any time you need “this OR that” logic, Power Query handles it with ease.

Troubleshooting Tips

• Didn’t see the “Advanced” option? Make sure you select a filter option like Begins With or Equals first. The Advanced button only appears in that dialog.
• Your filtered table didn’t update with new data? Remember to hit Refresh. Power Query doesn’t auto-refresh unless you tell it to.
• Columns renamed or missing? If your original data changes (like column headers), Power Query might break. To avoid this, try to keep your source table structure consistent.

 

FAQs

1. What is Power Query, and why should I use it?

Power Query is a data preparation tool in Excel that allows you to import, clean, and transform data from multiple sources. Unlike standard Excel tools, it automates repetitive tasks and allows complex transformations without writing formulas. It works in a step-by-step interface, saving each action for future use. This makes it ideal for handling large datasets efficiently. Essentially, Power Query turns manual data cleanup into a repeatable, automated process.

2. Can I filter records using Power Query with OR conditions?

Yes! Unlike Excel’s standard filters, Power Query allows you to combine multiple conditions easily. For example, you can filter rows where a name starts with “Y” OR the country equals “USA.” You do this using the Advanced filter option in the editor. Power Query handles both text and numeric conditions. Once set up, the filter is saved as part of your query.

3. Will my filtered table update if the source data changes?

Absolutely. Power Query is designed to refresh filtered results automatically. After updating your source table, just click Refresh in the Data tab. All the steps, including your OR filters, will be applied to the new data. This eliminates the need to redo manual filters or formulas. It’s especially helpful for recurring reports or dashboards.

4. Do I need to know programming to use Power Query?

No programming skills are required. Power Query uses a visual interface to define transformations. Each step you take—filtering, splitting, merging—is recorded and can be edited via the editor if needed. Behind the scenes, Power Query uses M code, but you only need to touch it if you want custom or advanced operations. Most users can do everything with clicks and menus.

5. What are common issues when filtering with Power Query, and how do I fix them?

Some common issues include the Advanced option not appearing, which usually happens if you haven’t selected a proper filter type first. Another is data not updating, which can be fixed by clicking Refresh. Changes in column names can break queries, so keep your table structure consistent. Power Query also won’t work if your source data has errors, blanks, or mismatched types—cleaning your data first helps. Overall, most problems are easy to troubleshoot once you understand the workflow.