What You’ll Learn
Power Query in Excel is the feature most analysts wish someone had told them about three years earlier — and it is sitting on your Data tab right now, completely free, doing absolutely nothing because nobody pointed at it and said: use this.
Here is the scenario. It is Monday morning. You have six CSV files in your downloads folder, each one from a different regional team. Same columns, different data. You open the first one, copy the rows, paste them into your master sheet, delete the header row that copied across, fix the date format that changed for some reason, move on to file two. Forty-five minutes later you have a combined table — and you have already done this exact task eighteen times this year.
Power Query ends that loop. You set the process up once, and every time the data updates, you click Refresh. That is the whole pitch. This lesson covers everything from connecting to your first data source to merging tables, cleaning messy exports, and building a refresh workflow that runs itself.
What Is Power Query in Excel
Power Query in Excel is a built-in data transformation tool designed to handle what data professionals call ETL — extract, transform, and load. In plain terms: it connects to your data, cleans it up, and delivers it to your worksheet in whatever shape you need.
Every transformation you make is recorded as a replayable step. Next time your source data changes, Power Query follows the same instructions from top to bottom and delivers the same clean result. Your original data is never touched.
Power Query vs Get and Transform — Are They the Same Thing?
Yes, completely. Microsoft rebranded Power Query as “Get and Transform” when they built it into Excel 2016. You will see both names used interchangeably online and on Microsoft’s own documentation. The tool, the interface, and the M language underneath are all identical. If someone says “Get and Transform,” they mean Power Query.
How Power Query Fits Into Your Excel Workflow
Think of Excel’s capabilities in layers. Formulas calculate. Pivot tables summarise. Macros automate repetitive clicks. Power Query cleans and shapes raw data before any of that other work begins.
The mistake most Excel users make is trying to fix bad data using formulas. You end up with TRIM functions in helper columns, nested IF statements trying to handle blanks, and VLOOKUP chains that collapse whenever someone decides to insert a column. Power Query solves the problem upstream, before the data ever reaches your worksheet.
When to Use Power Query — and When Not To
Use Power Query when your data comes from an external source that changes regularly, when you are combining multiple files or tables, when the same cleaning job needs to happen repeatedly, or when you are replacing a manual process you are genuinely tired of doing.
Do not use Power Query for simple one-off lookups, for calculations that need to react dynamically to cell inputs, or for situations where a formula takes ten seconds and Power Query would take ten minutes to set up. It is a pipeline tool, not a calculator. Knowing when not to use it is just as valuable as knowing how.
How to Open the Power Query Editor
Go to the Data tab on the ribbon. In the Get and Transform Data group, click Get Data. Choose your source type from the menu. Once you select a source and load a preview, the Power Query Editor opens in a separate window.
Everything you do to your data happens in this editor window. Nothing lands in your worksheet until you click Close and Load. That staging separation is the whole point.
Finding Power Query in Different Excel Versions
A Quick Tour of the Query Editor Interface
The editor has four areas worth knowing before you touch anything.
- The ribbon at the top contains all transformation commands organised across Home, Transform, Add Column, and View tabs.
- The data preview in the centre shows you what your table looks like right now, after the steps you have applied so far.
- The Applied Steps pane on the right records every action you take. This is the most important part of the entire editor — more on this shortly.
- The formula bar above the preview shows the M code behind whichever step is currently selected.
Excel Import Data with Power Query — Connecting to Sources
Power Query can connect to more data sources than most analysts will ever need. The ones you will use most often are Excel files, CSV and text files, folders of multiple files, websites with data tables, and databases.
Importing from Excel Files and CSVs
For an Excel file: Data tab → Get Data → From File → From Workbook. Navigate to the file, choose the table or sheet you need, and click Transform Data to open the editor. Do not click Load directly — always go through Transform Data first so you can check what you are about to bring in.
For a CSV: Data tab → Get Data → From File → From Text/CSV. Power Query detects the delimiter automatically. Same rule applies — Transform Data, not Load.
Connecting to a Folder (Combining Multiple Files Automatically)
This is where Power Query starts doing things that genuinely cannot be replicated with formulas.
If you have twelve monthly sales files sitting in a folder and you need to combine them, use Get Data → From File → From Folder. Power Query lists every file in that folder and lets you combine them with a few clicks. It appends all the data into one table and, if you want it, adds a column showing which file each row came from.
When next month’s file lands in the same folder, you refresh. That is all you do. The new file is picked up automatically.
Importing from the Web and Databases
For a public webpage with a data table: Get Data → From Other Sources → From Web. Paste the URL. Power Query detects tables on the page and lets you choose which one.
For a database: Get Data → From Database, then select your database type. You will need credentials, but once the connection is established it persists and refreshes on demand. Power Query supports SQL Server, Oracle, MySQL, PostgreSQL, and more.
Power Query Steps — The Applied Steps Pane Explained
Stop here for a moment, because if you understand this one concept thoroughly, everything else in Power Query makes sense.
Every single action you take in the Power Query editor gets recorded as a named step in the Applied Steps pane. Change a data type — that is a step. Remove blank rows — that is a step. Split a column — that is a step. They stack up in sequence, and when you refresh, Power Query runs through all of them from top to bottom on your fresh source data.
What Applied Steps Actually Means
Think of it as a cooking recipe. Your raw data is the ingredients. Each step is one instruction in the method. Every time you refresh, the recipe runs on whatever ingredients are currently in the pot.
This means your original data is never modified. Power Query works on a copy in the background, applies all your transformations, and loads the result into your worksheet. If something goes wrong, your source is untouched.
How to Edit, Delete, and Reorder Steps
To edit a step, click on it in the Applied Steps pane. The data preview shows you the state of your table at that exact point. Click the gear icon next to the step name to modify its settings.
To delete a step, click the X next to it. Be careful — if a later step depends on something the deleted step created, you will get an error downstream.
To reorder steps, right-click and choose Move Up or Move Down.
💡 PRO TIP
Rename your steps to something meaningful. Right-click any step and choose Rename. Removed Columns tells you what happened but not why. Removed Irrelevant Audit Columns actually means something when you revisit this query six months later.
Why This Is Better Than a Macro
Trust me on this — if you have been using recorded macros for data cleaning, try the equivalent in Power Query once and you will not go back.
Macros record clicks and cell references. The moment your data structure shifts — an extra column appears, a sheet gets renamed — the macro throws an error. Power Query steps reference columns by name, give you a visual preview at every stage, and tell you exactly which step failed and why when something goes wrong.
Essential Power Query Transformations — Excel Power Query Tutorial
These are the transformations that show up in real work, on real datasets, constantly.
Assigning Data Types — Why You Must Do This First
When Power Query imports your data, it takes an educated guess at each column’s data type. A date column that looks like 01/15/2024 might come in as text. A decimal value might be imported as a whole number. Always set data types before doing anything else — click the type icon to the left of any column header and choose the correct one.
⚠️ COMMON MISTAKE
Skipping data type assignment and then filtering a date column that Power Query treated as text. Your filter will not behave the way you expect because text that looks like a date and an actual date value are completely different things to Power Query. Set types first, always.
Removing Blank Rows, Duplicates, and Errors
Blank rows: Home tab → Remove Rows → Remove Blank Rows.
Duplicates: select the key columns you want to check, then Home tab → Remove Rows → Remove Duplicates.
Error rows: Home tab → Remove Rows → Remove Errors. Each of these becomes a step that runs automatically on every refresh.
Splitting and Merging Columns
You have a Full Name column and you need First Name and Last Name separately. Select the column, Transform tab → Split Column → By Delimiter. Choose space. Power Query creates two new columns.
To combine two columns, go to Add Column → Custom Column and use a formula:
[First Name] & ” ” & [Last Name]
You will get a new column with both values joined, and you can name it whatever you like.
Unpivoting Data — The Transformation Most People Miss
You’ve probably seen this kind of spreadsheet: months running across the top as column headers — January, February, March — with values underneath. It looks clean and readable. It is completely useless for analysis.
What you need is a single Month column and a single Value column, with each combination on its own row. Select all the month columns, right-click, choose Unpivot Columns. Power Query restructures the entire table in under a second. No manual reformatting. No helper columns. No formulas.
This is the transformation that makes analysts genuinely gasp the first time they see it.
Adding Conditional Columns (Without Formulas)
If you want a column that categorises values — High, Medium, or Low based on a sales figure — you do not need to write a nested IF formula. Go to Add Column → Conditional Column and build the logic using a visual interface with dropdowns. Power Query writes the M code for you in the background.
Power Query Merge — Replacing VLOOKUP for Good
Power Query Merge does what VLOOKUP does — but without the column-counting, without the fragility when someone inserts a column, and without the silent errors when a match is not found.
If you are currently using VLOOKUP for joining two tables together, check out the VLOOKUP lesson on XplorExcel.com — it is worth understanding both tools so you can choose the right one. For anything beyond a simple one-off lookup, Merge is the better long-term choice.
Merge vs Append — Understanding the Difference
Merge combines tables horizontally. You have Table A and Table B, they share a common ID column, and you want to bring columns from Table B into Table A where the IDs match. That is a merge.
Append stacks tables vertically. You have January sales and February sales in separate tables with the same columns, and you want them combined into one. That is an append.
Left Outer, Inner, and Anti Joins — Which One Do You Need?
⚠️ COMMON MISTAKE
Choosing Inner join when you meant Left Outer. Rows with no match are silently excluded in an Inner join. You may not notice until a report total looks wrong and you spend an hour hunting for missing data.
Step-by-Step: Merging Two Tables in Power Query
How to Merge Two Tables
- Load both tables into Power Query as separate queries.
- Open the query you want to use as your base table.
- Go to Home tab → Merge Queries.
- Select the second query from the dropdown at the top of the dialog.
- Click the key column in the top table preview, then click the matching column in the bottom table preview.
- Choose your join type from the dropdown.
- Click OK. A new column appears in your table containing the merged data.
- Click the expand icon at the top of that column and select which columns from the second table to bring across.
- Click Close and Load.
🎯 TRY IT YOURSELF
Find any two tables in your own work that share a common ID — an orders list and a product catalogue, a staff list and a timesheet, a client list and a billing record. Load both into Power Query and perform a Left Outer Merge on the shared column. Compare what you get with what a VLOOKUP would produce. Pay particular attention to how unmatched rows are handled — Power Query makes them visible as nulls rather than hiding them as errors.
Refreshing Your Query — How Automation Actually Works
Refresh is the payoff for all the setup work. Without it, Power Query is a slightly more complex way to import data. With it, it is a self-updating data pipeline that you build once and run forever.
What Happens When You Click Refresh
Power Query reconnects to the original source, retrieves the current data, and runs every step in the Applied Steps pane from top to bottom. The cleaned result is loaded back into your worksheet. For most datasets this takes a few seconds. The source data never gets overwritten. Every refresh produces a consistent, reproducible output.
Refresh All vs Refresh Individual Queries
Right-click any query result table in your worksheet and choose Refresh to update just that one query. Go to Data tab → Refresh All to update every query in the workbook at once.
If you have queries that depend on each other — Query B uses the output of Query A — make sure Query A refreshes first. In complex workbooks, this dependency order matters.
Why Queries Break After Refresh (and How to Fix Them)
Two causes cover ninety percent of refresh failures.
The source file moved. Fix this by going to Data tab → Queries and Connections → right-click the query → Edit → Home tab → Data Source Settings to update the path.
The data structure changed. If your source table had a column called Region and someone renamed it to Sales Region, any step that references Region by name will throw an error. Edit the affected step to use the new column name.
Setting Up Automatic Refresh on File Open
Right-click your query in the Queries and Connections pane and choose Properties. Check the box labelled Refresh data when opening the file. From that point on, every time someone opens the workbook, Power Query pulls fresh data automatically before anything else loads.
Introduction to M Language — Just Enough to Be Dangerous
M is the language running behind every step in Power Query. You will not need to write it to handle most tasks, but you should be able to read it. The moment you can read M, you can troubleshoot almost any problem the editor throws at you.
What M Is and When You’ll Need It
Every step in the Applied Steps pane has an M expression behind it, visible in the formula bar. Clicking through the ribbon generates M code automatically. You only need to write or edit M manually when the visual interface cannot do what you need — complex conditional logic, custom transformations, or debugging a mis-generated step.
For the complete M function reference, the Microsoft Power Query documentation at learn.microsoft.com is the definitive resource and covers every built-in function with examples.
Reading a Custom Column Formula Without Fear
Here is what a basic M conditional looks like in a custom column:
if [Sales] > 10000 then “High” else if [Sales] > 5000 then “Medium” else “Low”
Column names go in square brackets. The logic follows an if-then-else structure. Once you can read that without anxiety, writing simple variations of it comes naturally.
Power Query in Excel — Real-World Workflow Examples
Example 1 — Combining Monthly Sales CSVs from a Folder
A sales analyst receives twelve regional CSV files every month in the same shared folder. Using Get Data → From Folder, she sets up a single query that combines all twelve automatically, appending them into one table and tagging each row with the source filename. When month thirteen’s files arrive, she opens the workbook, clicks Refresh, and the new data is included. The task that used to take forty minutes now takes ten seconds.
Example 2 — Cleaning a Messy CRM Export
A monthly CRM export arrives with dates stored as text, the Region column containing mixed capitalisation (“north”, “North”, “NORTH”), and blank rows scattered randomly. Power Query handles all three: a Changed Type step converts the date text to proper Date values, a Capitalise Each Word transform standardises the Region column, and Remove Blank Rows clears out the empties. Every month the same export refreshes through the same pipeline and arrives clean without anyone touching it manually.
Example 3 — Replacing a VLOOKUP Chain with a Merge Query
An analyst maintains an orders table and a product master. Six VLOOKUP columns broke every time anyone touched the product master’s column structure. The fix: two Power Query queries joined with a Left Outer Merge on the Product ID column. No column position counting, no #N/A errors, no fragility. When the product master updates, a single refresh keeps everything current and complete.
Once your data is clean and structured, the natural next step is building analytical models that go beyond what pivot tables can handle. The Power Pivot lesson on XplorExcel.com picks up exactly where this one leaves off.
Power Query vs Power BI — Should You Upgrade?
Here is the honest answer. Power Query in Excel and Power Query in Power BI run on the same M engine and look almost identical. Learning Power Query in Excel is learning a skill that transfers directly to Power BI with almost no additional friction.
The difference is in what you do with the clean data. Excel delivers it to a worksheet or data model for personal analysis. Power BI delivers it to interactive visual dashboards that can be shared across an entire organisation.
If your datasets are pushing into hundreds of thousands of rows, or if your output needs to be a shared interactive report rather than a workbook, Power BI is worth exploring. For most individual analysts who live in Excel workbooks, Power Query in Excel is more than enough.
Frequently Asked Questions
Is Power Query available in all versions of Excel?
It is built into Excel 2016 and later. Excel 2013 needs the free Microsoft add-in. Excel 2010 and earlier do not support it.
Does Power Query slow down Excel?
For most realistic datasets, no. The query only runs when you refresh, and the result loads into a table like any other data. Very large datasets or workbooks with many simultaneous queries can affect performance, but this is rarely a problem for typical analyst workloads.
Can Power Query connect to live databases?
Yes — SQL Server, Oracle, MySQL, PostgreSQL, and more. The connection refreshes on demand and pulls current data each time.
Is Power Query the same as VBA?
No. VBA is a programming language for automating Excel actions using code. Power Query is a visual transformation tool with its own language, M, running behind the scenes. Power Query is far more accessible to non-programmers and purpose-built for data transformation specifically.
📚 Further Reading
The official and authoritative reference for every Power Query function, connector, and M language feature — straight from Microsoft.
One of the most trusted independent Excel resources online, with practical Power Query worked examples including custom M formulas.