Written and maintained by CASRAI Editorial Board
Last updated
Power Query is the data import and cleaning engine built into Excel. You open it from the Data tab, choose a source, click Transform Data, and it records each cleaning action as a step; Close & Load then sends the result to a worksheet or the Data Model. The source file itself is never changed, which is the property that makes it useful for research data.
What Power Query is used for in research
Microsoft describes Power Query as “a data transformation and data preparation engine” with a graphical interface for getting data from sources and a Power Query editor for applying transformations. In practice, researchers use it to bring in instrument exports, survey downloads and spreadsheets from collaborators, and to fix the same structural problems every time those files arrive: stray header rows, text stored as numbers, inconsistent column names, and several files that need stacking into one table.
The feature that matters most for research is how it records work. Power Query saves transformations as query steps and applies them when the query runs, and it does not modify the source data. Each step you create in the editor also generates the underlying code, written in the M language. Together, the Applied Steps list and the M script form a written, replayable cleaning log. You can keep the raw file untouched, point to the query as the record of what was done to it, and re-run it when a corrected export arrives. That is a stronger audit trail than hand edits in a worksheet, and it supports the same goals as documenting data provenance and computational reproducibility.
Beyond basic clean-up, the editor offers merge, append, group by, pivot and unpivot, and Microsoft states that Power Query supports hundreds of data sources and over 350 types of data transformations.
Install or access (official source only)
There is nothing separate to install in current Excel. Microsoft’s support documentation says Power Query is available in Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016, on Windows, Mac and the web. Three details are easy to miss:
- On Windows, Power Query requires .NET Framework 4.7.2 or later and the WebView2 Runtime.
- Microsoft states: “There is no support for Power Query on Excel 2016 and Excel 2019 for Mac.”
- In Excel 2010 and 2013 it was a free add-in, and that add-in is now deprecated.
Microsoft distinguishes two experiences. Power Query Desktop is found in Excel and Power BI Desktop. Power Query Online is found in Dataflow Gen2, Power BI dataflows, Power Platform dataflows and Excel Online. Microsoft’s availability table lists Excel for Windows as having the Desktop experience and Excel for Mac as having Desktop as well. Connectors, authentication and load destinations differ by host product, so a tutorial written for Power BI will not always match what you see in Excel.
Step-by-step: import and clean a file
These steps follow Microsoft’s current Excel documentation for importing data with Power Query. Exact labels can shift between Excel builds, so match them against your ribbon.
- Keep an untouched copy of the raw file in its own folder. Power Query reads from it and never writes back to it.
- In Excel, open the Data tab and choose Get Data (Power Query). The Choose data source dialog opens. In older builds the path is Data > Get Data, followed by a category such as From File, From Database, From Azure, From Online Services or From Other Sources.
- Select your source (for example a workbook, text or CSV file) and connect to it. A Navigator window lists the tables or sheets it found.
- Select the table you need, then choose Transform Data to open the Power Query editor. Choosing Load instead, at the bottom right, loads the data to a worksheet without opening the editor.
- In the editor, apply your clean-up actions: filter rows, rename or remove columns, change data types, and use merge, append, group by, pivot or unpivot where the data needs reshaping. Each action appears as an entry in the Applied Steps list in the Query Settings pane.
- When the table looks right, load it. In the editor, go to the Home tab, then the Close group, and choose Close & Load. Alternatively, in the Queries & Connections pane, right-click the query and choose Load To to pick a destination.
Excel’s Query Options (under File > Options and settings) set whether loaded data goes to a worksheet or the Data Model. For survey or lab data headed for analysis in another package, a worksheet table is usually enough; the Data Model suits larger tables you plan to relate to each other.
Output and how to read it
Two things come out of a query. The first is the loaded table, which behaves like an Excel table in your workbook. The second is the recipe.
- Applied Steps. This list in Query Settings is the cleaning log in order. Click any step to see the data as it stood at that point, which is the quickest way to find where a value changed or a row disappeared.
- The M script. Open the Advanced Editor to see, and edit, the code Power Query generated. For a methods section or a data-management record, this script is the precise description of what was done, and it can be stored with the project.
- Refreshing. Microsoft states that when you refresh a query, each step runs automatically. A corrected export saved over the same source file therefore flows through the same cleaning without redoing the clicks.
Keep the output tidy for the next stage: one variable per column, one observation per row, as described in tidy data rules for research datasets. For the general sequence of checks that should surround any cleaning pass, see data cleaning steps.
Troubleshooting
I cannot find Get Data (Power Query) on my Mac
Microsoft states there is no Power Query support in Excel 2016 or Excel 2019 for Mac. Current Microsoft 365 Excel on Mac is listed as having the Desktop experience, so check your Excel version first.
The editor will not open or loads blank on Windows
Windows needs .NET Framework 4.7.2 or later and the WebView2 Runtime. Confirm both are installed before looking elsewhere.
A connector or sign-in option from a tutorial is missing
Connectors, authentication and destinations differ by host product. A tutorial for Power BI or Dataflow Gen2 describes Power Query Online or a different host, and Excel may offer fewer or different options.
My raw file seems to have changed
Power Query does not modify the source. If the original appears altered, the edit happened outside the query, for example in the loaded worksheet copy or in the file itself in another program. Restore from your untouched copy and rebuild from the query.
Free alternatives
If you need cleaning that lives outside a spreadsheet, or your institution does not license Excel, these are free options. The descriptions below come from each project’s own documentation.
| Tool | What its own documentation says it does | Fits when |
|---|---|---|
| OpenRefine | OpenRefine describes itself as a “powerful free, open source tool for working with messy data.” It offers merging of similar values, facets for filtered views, and a way to rewind to any previous state and replay your operation history on a new version of the dataset. | You want a point-and-click interface and a replayable history for inconsistent text values. |
| pandas (Python) | A BSD 3-Clause licensed Python package with handling of missing data, merging and joining of data sets, reshaping and pivoting, and tools for loading CSV, delimited and Excel files. | You already write code and want the cleaning script under version control. See Data Version Control for research datasets. |
FAQ
Does Power Query change my original data?
No. Microsoft states that Power Query records transformations as query steps and applies them when the query runs; it does not modify the source data.
Is Power Query the same as the M language?
No. Power Query is the engine and editor. When you create a transformation step, it automatically writes the M code for that step, which you can view and edit in the Advanced Editor.
Which Excel versions have Power Query?
Excel for Microsoft 365, 2024, 2021, 2019 and 2016, on Windows, Mac and the web, except Excel 2016 and 2019 for Mac. Excel 2010 and 2013 used a now-deprecated free add-in.
Can I reuse the same cleaning on a new file?
Yes. Because each step runs automatically when a query is refreshed, a new export that has the same structure can pass through the same steps. Keep the file layout consistent so the steps still apply.
Where does the cleaned data go?
To a worksheet or the Data Model, depending on how you load it: Close & Load from the editor, or Load To from the Queries & Connections pane.
Sources (official documentation)
- Microsoft Learn, What is Power Query? (page dated 2026-09-30, checked 2026-10-29).
- Microsoft Support, About Power Query in Excel (checked 2026-10-29).
- Microsoft Support, Import data from external data sources (Power Query) (checked 2026-10-29).
- OpenRefine, openrefine.org (checked 2026-10-29).
- pandas, Package overview (checked 2026-10-29).
