Power Query is Excel's data-preparation tool: you go to the Data ribbon, select Get Data, pick your source, clean it up in the Power Query editor, and every cleanup step is saved as a query you can refresh next time instead of redoing by hand. It never changes your source data; it only changes the copy it loads into Excel.
Microsoft calls Power Query a data transformation and data preparation engine. It has a graphical interface for getting data from sources and an editor for applying transformations. If you export the same report every week and spend half an hour deleting columns and fixing dates before you can use it, this is the tool that gets that half hour back.
What Power Query is for
Microsoft lists the problems it was built to solve, and they'll sound familiar to anyone who's wrestled with exported data:
- Finding and connecting to data is too difficult.
- Connecting to different sources works in different, fragmented ways.
- Data needs reshaping before you can use it.
- Any shaping you do is one-off and not repeatable.
- The volume, velocity and variety of data keep growing.
The fourth one's the big one for most small businesses. A formula gives you a result; Power Query gives you a repeatable recipe for getting the data ready in the first place.
On scale, Microsoft says Power Query connects to hundreds of data sources and offers over 350 types of data transformations for each one. You can also define your transformations against a subset of the data, then refresh the query to run them on the lot.
Step 1: get your data in
In Excel for Windows or Mac, go to the Data ribbon and select Get Data. Scroll through the categories in the menu, choose the connector you want, then fill in the access information it asks for. In Excel for Windows you can also go straight to Text/CSV, Web or Excel worksheet data without opening the Get Data menu.
Here's the catch to check before you plan a workflow. Not every version of Excel supports the same Power Query connectors. Microsoft keeps a separate list of the connectors supported by each version of Excel for Windows and Excel for Mac, so if your process depends on one particular source, check that your version has it before you build around it.
Step 2: clean the data in the editor
You don't need to write code. Microsoft says the editor lets you apply transformations by clicking ribbons, menus and buttons. Each time you create a step, Power Query automatically writes the code for it in M, its own formula language. If you ever want to see or edit that script, it's in the Advanced Editor, but plenty of people use Power Query for years without opening it.
Step 3: read the Applied steps list
This is what makes Power Query different from cleaning data by hand. It records every transformation as a query step and applies the steps when the query runs. Your source isn't touched.
Every step appears in the Applied steps list in the Query Settings pane. Select any step and you see the data as it looked at that point, which makes it easy to spot where something went wrong. If the pane's closed in Excel, go to the View tab and select Query Settings, and it opens on the right.
Step 4: combine tables with Append or Merge
Both live on the Home tab in the Combine group, and they do very different jobs.
| Task | Where in Power Query | What happens |
|---|---|---|
| Get data | Data ribbon > Get Data | Choose a connector, enter access details |
| Clean data | Power Query editor ribbons and menus | Each action becomes an Applied step |
| Stack tables | Home > Combine > Append queries | Rows from several tables become one table |
| Join tables | Home > Combine > Merge queries | Tables joined on matching columns |
| Update later | Refresh the query | Steps re-run on fresh data |
Append stacks tables on top of each other. It creates a single table by adding the contents of one or more tables to another, combining the column headers into one set. If one table has a column the other doesn't, the rows from the second table show null in that column. Choosing Append queries changes the current query and leaves the other one unchanged. It's the tool for twelve monthly exports that need to become one year.
Merge joins tables side by side on matching values in one or more columns, using a join kind you choose. There are two options: Merge queries, where the current query becomes the left table, and Merge queries as new. Left and right order matters when you pick the join kind. If you've been using VLOOKUP to pull columns from one table into another, Merge is the Power Query way to do the same job once and refresh it.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Step 5: load it and refresh it
Power Query transforms data, but it doesn't store it in a destination of its own. The product hosting it decides where the results load, and in Excel that's back into your workbook. From then on, when the source changes, you refresh the query and every step runs again on the new data.
- Open Excel and go to the Data ribbon.
- Select Get Data and choose a connector, such as a file, the web or a database.
- Pick your table and open it in the Power Query editor.
- Apply your cleanups from the ribbons and watch each one appear under Applied steps.
- Load the result to Excel, and refresh the query whenever the source changes.
Once the data's clean, the usual next move is to summarise it, and a pivot table built on a Power Query table updates every time you refresh.
Power Query Online and Desktop
Microsoft describes two experiences. Power Query Desktop is the one inside Excel and Power BI Desktop. Power Query Online runs in the browser, in Excel Online, Power BI dataflows, Power Platform dataflows and Dataflow Gen2 in Microsoft Fabric. They share the core editor, but connectors, sign-in methods and where the data lands can differ depending on which product you're in. If a workflow works on your laptop, don't assume it'll behave identically online.
Here's the related guide: this walkthrough of how to use the SUBTOTAL function in Excel.
My verdict
If you repeat the same cleanup every week, Power Query is the thing to learn next, before any new formula. I think of it this way: formulas are for the result, Power Query is for the prep. Build the steps once, check them in the Applied steps list, and let a refresh do the boring part from then on.
Related reading: this walkthrough of how to calculate days between dates in Excel.
Frequently asked questions
Where is Power Query in Excel?
On the Data ribbon. Select Get Data, choose a connector and the Power Query editor opens when you transform the data.
Does Power Query change my original data?
No. Microsoft says it records transformations as steps and applies them when the query runs; it doesn't modify the source.
Do I need to know code to use Power Query?
No. You click ribbons and menus, and Power Query writes the M code for each step automatically.
What's the difference between Append and Merge?
Append stacks tables into one longer table. Merge joins two tables side by side on matching column values.
Why can't I find a connector in my Excel?
Not every Excel version supports the same connectors. Microsoft keeps a list by version for Windows and Mac.
