Power Query Editor in Power BI - What It Does and How to Use It Well

Every Power BI project we've worked on hits the same wall. Someone connects their data, drags a few fields onto a report page, and then realises the data isn't quite right. Sales figures are stored as text. Date columns have three different formats. Customer names are duplicated with slightly different spelling. The report looks fine at a glance, but the numbers don't add up.

This is where Power Query Editor earns its keep. It's the data preparation tool built into Power BI Desktop, and it's probably the most underrated part of the entire Power BI stack. If you're building reports that people will rely on for actual business decisions, understanding Power Query Editor isn't optional.

What Power Query Editor Actually Is

Before we get into the details, it helps to understand where Power Query Editor fits within Power BI Desktop. There are three main views in Desktop:

Report view is where you build your visualisations: charts, tables, cards, the things people actually see and interact with. Table view (sometimes called Data view) shows you the data in your model in tabular form, where you can add measures, create calculated columns, and inspect values. Model view gives you a visual representation of how your tables relate to each other.

Power Query Editor sits before all of that. It's where you connect to your data sources, clean the data, reshape it, and prepare it before it ever reaches your data model. The report is what people see, but Power Query is where you make sure the underlying data is actually worth looking at.

To open it, you click Transform data on the Home tab in Power BI Desktop. A separate window opens with its own ribbon, panes, and workflow. It looks different from the main Desktop interface, and that throws some people off the first time they see it.

The Interface, Quickly

The Power Query Editor window has four main areas, and they're all doing different jobs:

The ribbon across the top has tabs for Home, Transform, Add Column, View, Tools, and Help. Most of the data transformation operations live under Transform and Add Column. The Home tab is where you connect to new data sources and close the editor when you're done.

The Queries pane on the left lists all the queries (tables) in your project. Each query represents a connection to a data source and the transformations applied to it. Click a query to see its data and work with it.

The Data pane in the centre shows the actual data for whichever query you've selected. This is where you'll spend most of your time. You can right-click column headers to access transformation options like changing data types, removing columns, splitting values, and filtering rows.

The Query Settings pane on the right is quietly the most important part. It shows the Applied Steps for the selected query, which is a sequential record of every transformation you've made. Each step is listed in order, and you can click any step to see what the data looked like at that point. You can rename steps, reorder them, or delete them.

That Applied Steps list is worth paying attention to. Every time you do something in Power Query (filter a column, change a data type, merge two tables) it records a step. This means your data transformations are documented and repeatable. When someone asks "why is this number different from the source system?", you can walk through the Applied Steps and show exactly what happened.

What It's Good At

Power Query Editor handles the messy reality of business data. Here's where we see it used most in our Power BI consulting work:

Connecting to almost anything. Power Query supports hundreds of data connectors. SQL Server, Excel, SharePoint, web APIs, Azure SQL, Dataverse, SAP, Oracle, flat files, folders of CSVs. The list goes on. For most Australian businesses, the data sources they need are covered out of the box.

Cleaning inconsistent data. This is the bread and butter. Trimming whitespace from text fields. Replacing null values. Standardising date formats across sources that use DD/MM/YYYY and MM/DD/YYYY (a constant headache for Australian teams working with American systems). Removing duplicate rows. Fixing text casing. These operations are point-and-click, no code required.

Combining data from multiple sources. One of the more powerful features is the ability to merge (join) and append (union) queries. If you have customer data in one system and transaction data in another, you can join them in Power Query before the data hits your model. This is often simpler than trying to build relationships between imperfect tables in the data model.

Reshaping data. Unpivoting columns is probably the single most useful transformation in Power Query, and most people don't know it exists. If you've ever received a spreadsheet where months are columns (Jan, Feb, Mar...) instead of rows, unpivot fixes that in two clicks. Pivot does the reverse. Both are essential when your source data is structured for human reading rather than analysis.

Handling folders of files. Point Power Query at a folder containing multiple Excel files or CSVs with the same structure, and it will combine them into a single table automatically. When new files are added to the folder, they're picked up on the next refresh. We've seen this save hours of manual data consolidation for businesses running monthly reporting cycles.

The Applied Steps Pattern

The step-based approach is worth dwelling on, because it's different from what most people expect.

When you change a column's data type in Power Query, you're not modifying the source data. You're adding an instruction that says "when loading this data, treat this column as a whole number instead of text." The underlying data in your database or spreadsheet remains unchanged.

That matters more than it sounds. You can experiment freely without fear of breaking anything. Delete a step you don't like. Reorder steps to see if the output changes. Go back to an earlier step and branch off in a different direction. The source system is never touched.

It also means your transformations are self-documenting. We encourage our clients to rename their Applied Steps to plain English descriptions like "Remove blank rows", "Convert dates to AU format", "Join with customer master". When someone else inherits the report six months later (and someone always does), they can understand what each step does without reverse-engineering the logic.

The Advanced Editor and M Language

Behind every step in Power Query is a line of code written in M, the Power Query formula language. You don't need to know M to use Power Query productively. The graphical interface generates M code for you, and most users never look at it.

But M is there when you need it. You can open the Advanced Editor from the View tab and see the complete query as M code. For simple transformations, the generated code is fine. For more complex logic (custom functions, conditional transformations, recursive operations, parameterised queries), writing M directly gives you capabilities the GUI doesn't expose.

Our take: M is powerful but idiosyncratic. It doesn't look like SQL, Python, DAX, or really anything else. The syntax takes getting used to, and the error messages aren't always helpful. If you have someone on your team who's comfortable with coding, they'll pick it up. But don't expect every business analyst to write M from scratch.

For most organisations, the sweet spot is using the GUI for 90% of transformations and dipping into M for the occasional custom function or complex conditional logic. If you find yourself writing M constantly, it might be a sign that your data source needs work or that you should be handling some of that transformation upstream.

If you're curious about how Power Query connects with Microsoft Data Factory for more complex ETL pipelines, it's worth understanding where each tool's responsibility starts and ends.

Common Mistakes We See

After years of reviewing Power BI implementations for Australian businesses, the same patterns keep showing up:

Not using Power Query at all. Some teams skip straight to the data model and try to fix data quality issues with DAX calculated columns. DAX runs at query time, which makes reports slower. Power Query runs at refresh time, so the data is already clean when the model loads. If you can do something in Power Query or DAX, Power Query is almost always the better choice.

Too many steps doing too little. We've seen queries with 40+ steps where each step makes a tiny change. This isn't wrong, but it makes the query harder to understand and can slow down refresh times. Group related operations where it makes sense. Remove columns you don't need early in the query to reduce the data that flows through subsequent steps.

Ignoring data types. Power Query tries to detect data types automatically, and it often gets them wrong. A column of postcodes might get detected as whole numbers, which strips leading zeros (goodbye, Victorian postcodes starting with 0). A column containing "TRUE" and "FALSE" might stay as text. Always check your data types explicitly and set them intentionally.

Not folding queries. Query folding is when Power Query translates your transformation steps into native queries that run on the source system (like SQL queries against a database). When query folding works, the database does the heavy lifting and only sends you the result. When it breaks, Power Query downloads all the data and processes it locally, which can be extremely slow for large datasets. Some steps break query folding (custom M functions, for example), so pay attention to where in your step sequence the folding stops.

Duplicating logic across queries. If five queries all need the same transformation (say, converting state abbreviations to full names), create a shared reference table or a custom function in M rather than repeating the logic in each query. This is basic DRY principle, and it applies here just as much as it does in software development.

When to Save and Close

When you're done shaping your data, click Close & Apply on the Home tab. This loads the transformed data into the Power BI data model, where you can build relationships between tables, write DAX measures, and start building report pages.

Your work is saved in the .pbix file, both the report and the Power Query definitions. When you hit refresh, Power Query re-runs all your steps against the current source data. If the source data has changed (new rows, updated values), those changes flow through your transformations and into the model automatically.

This refresh-and-transform cycle is what makes Power Query so useful for ongoing reporting. You build the transformation logic once, and it applies every time the data refreshes. No manual data prep, no copy-pasting between spreadsheets, no "I forgot to remove the test rows" moments at 4pm on a Friday.

Getting the Most Out of It

Power Query Editor doesn't get the attention that visuals and DAX do in the Power BI world. We think it deserves more. A beautiful report built on messy data is worse than useless because it's actively misleading. Power Query is where you make sure the foundation is solid.

If you're building Power BI reports for your organisation and you've been treating Power Query as an afterthought, spend some time getting to know it properly. The Microsoft Learn documentation has a detailed overview that covers the fundamentals well.

And if your data preparation needs are outgrowing what Power Query can handle, if you're dealing with massive datasets, complex orchestration, or data from dozens of sources, that's when it makes sense to look at dedicated data engineering tools like Microsoft Fabric and Data Factory. Power Query is great for report-level data prep, but it's not a replacement for a proper data platform.