Power BI Power Query Editor Basics
Real-world data is rarely clean and ready to use. Column names are unclear, dates are stored as text, numbers have extra spaces, and some rows contain errors or blanks. Power Query Editor is the workspace inside Power BI Desktop where you fix all of these problems before your data enters the report.
What Is Power Query Editor
Power Query Editor is a dedicated data preparation tool built into Power BI Desktop. You use it to view, clean, reshape, and transform data. Every step you take inside Power Query is recorded automatically, creating a repeatable recipe of transformations.
Think of it like a car wash. When you drive your car in (raw data), it is dirty and has scratches. The car wash applies a series of steps — soap spray, scrub brushes, rinse, wax, dry. Each step has a specific purpose. When the car exits (loaded into Power BI), it is clean and polished. Power Query is that car wash for your data — and it remembers every step so the next car (next data refresh) gets the same clean treatment automatically.
How to Open Power Query Editor
From the Home tab in Power BI Desktop, click Get Data, connect to your source, and in the Navigator window click Transform Data instead of Load. Power Query Editor opens in a separate window.
You can also open it later by clicking the Transform Data button in the Home tab ribbon even after data is already loaded.
The Power Query Editor Interface
Power Query Editor has its own ribbon and panels, separate from the main Power BI Desktop interface. Here are the main areas:
Queries Pane (Left Side)
All the tables (called queries) you have connected to appear here as a list. Click on a table name to select it and see its data in the center preview. You can rename, duplicate, or delete queries from this panel.
Data Preview (Center)
The main area shows a sample of your data — typically the first few hundred rows. You can see column names at the top and data values below. Column headers show the data type icon on the left — for example, a "123" icon for whole numbers, an "ABC" icon for text, and a calendar icon for dates.
Query Settings Pane (Right Side)
This pane has two sections. The top section shows the query name. The bottom section — labeled Applied Steps — shows every transformation you have applied, listed in order from top to bottom. This is the most important panel in Power Query.
Formula Bar
Above the data preview, a formula bar shows the M language code behind each applied step. M is the programming language that Power Query uses under the hood. You do not need to write M code manually — Power Query writes it for you as you click. But seeing it helps advanced users understand and modify transformations.
Applied Steps — The Heart of Power Query
Every action you take in Power Query becomes a step in the Applied Steps list. The steps execute in order from top to bottom every time your data refreshes.
For example, if your raw data has these problems:
- Column names are unclear
- A date column is stored as text
- Some rows have blank values
- A price column has values with currency symbols like "$" that prevent calculations
You fix each problem one by one. Power Query records four steps. When new data arrives next month and you refresh, Power Query applies all four steps automatically to the new data — no manual work needed.
This is like a factory assembly line. Each station on the line performs one specific task. Every product that comes in goes through all stations in the same order. The factory does not need to manually handle each product individually.
Common Tasks You Perform in Power Query
Renaming Columns
Double-click any column header to rename it. Change "Amt" to "Sales Amount" or "Cust_ID" to "Customer ID" so your report labels make sense to readers.
Changing Data Types
Click the data type icon on the left of a column header to change its type. If a date column shows as text, change it to Date. If a number column shows as text, change it to Whole Number or Decimal Number.
Removing Columns
Right-click any column header and select Remove. You can also select multiple columns with Ctrl+Click and remove them together. Removing columns you do not need makes your data model smaller and faster.
Filtering Rows
Click the small arrow on any column header to open a filter dropdown. Choose which values to keep or exclude. For example, filter a Status column to keep only "Completed" orders and remove "Cancelled" or "Pending" rows.
Promoting Headers
Sometimes data arrives with the column names in the first data row instead of the header row. Click Use First Row as Headers in the Home tab to fix this in one click.
The Close and Apply Button
When you finish making changes in Power Query Editor, click the Close and Apply button in the top left of the ribbon. This closes the editor and applies all your transformations to the data that loads into Power BI Desktop. Your report then uses the clean, transformed version of your data.
Key Points
- Power Query Editor is the data cleaning and transformation workspace inside Power BI Desktop.
- Every transformation you apply is saved as a step in the Applied Steps list.
- Steps run automatically in order every time you refresh your data — no manual rework needed.
- Common tasks include renaming columns, changing data types, removing columns, and filtering rows.
- Click Close and Apply when done to load the cleaned data into your report.