How to import and clean data in a spreadsheet with Power Query
Power Query, the Get & Transform tool in Excel, records each change you make to imported data as a step, and runs every step again when the source changes. It earns its place on a report rebuilt from the same export every week or month. For a one-off tidy of data already in the sheet, a formula is often quicker.
9 October 2026 · 8 min read
What Power Query is
Power Query is the import and clean-up tool built into Excel for Windows since Excel 2016, under Data → Get & Transform Data. Before that it was a free add-in for Excel 2010 and 2013. Power BI loads its data with the same engine, and recent versions of Excel for Mac include it too, with fewer sources.
A query is a source and a list of steps. The source can be a CSV file, a table in the workbook, another workbook, a folder of files, a database or a web page. It opens in the Power Query Editor, which shows a preview of the data, and every change you make there is added to the Applied Steps list down the right-hand side. Each step is a line of Power Query’s own formula language, M, and Home → Advanced Editor shows the whole query written out.
A query never changes its source. Close & Load writes the result as a new table, on a sheet of its own unless you say otherwise, and the file it came from stays as it was.
Google Sheets has nothing built in that works quite like it, though its IMPORTDATA and QUERY functions cover part of the same ground with formulas.
Steps that run again on refresh
The reason to record steps is that you do the cleaning once. Say your bank or your shop system gives you a CSV of last month’s transactions. The first time, you build a query that removes the columns you never use, gives the dates and amounts their proper types, filters out transfers between your own accounts and groups the rest by category. Next month you save the new export over the old file, under the same name, and choose Data → Refresh All (Ctrl+Alt+F5). Every step runs again on the new rows.
Clicking a step in Applied Steps shows the data as it stood after that step, which is the quickest way to find where a value went wrong. A step can be deleted, or a new one put in between two others, and Excel warns when a change may break the steps after it.
Steps name columns, and that is where refreshes fail. If the source renames or drops a column, the step that names it stops with an error such as “The column ‘Amount’ of the table wasn’t found.” Excel adds a Changed Type step by itself when it opens a CSV, and that step names every column, so it is often the first to break. Edit the step, or delete it and make it again.
A query refreshes when you ask it to. Its properties, from a right-click in the Queries & Connections pane, can make it refresh whenever the file opens, or every so many minutes.
The transformations you will use most
Most clean-ups come from a short list. In Excel each is on one of the editor’s tabs, or on the menu a column’s heading opens with a right-click.
- Use First Row as Headers, and the data types. Get these right before anything else. For dates written day first, as in Britain, right-click the heading and choose Change Type → Using Locale with the right country, or 03/04/2026 may come in as the 4th of March.
- Remove Columns and Remove Other Columns. Removing the columns you do not want names each of them in the step. Picking the ones you want and choosing Remove Other Columns names only those, so a column the source gains later is dropped without breaking anything.
- Filter and Remove Duplicates. Filter works from the arrow on a column’s heading, much as in a sheet. Remove Duplicates keeps the first row of each set that matches in the columns you picked.
- Split Column and Trim. Split cuts a column at a delimiter, such as the comma between a surname and a first name. Power Query’s Trim takes spaces off the ends of a value only, whereas Excel’s TRIM function also closes up double spaces in the middle.
- Fill Down and Unpivot Columns. Fill Down copies a label into the blank cells under it, which suits reports that print a group’s name once. Unpivot turns a column per month into a row per month, the shape a pivot table or a chart wants.
- Group By, with a count, sum, average, minimum or maximum for each group.
- Merge Queries and Append Queries. Merge is a lookup between two tables on a matching column. Left Outer keeps every row of the first table, Inner keeps only the rows that match, Left Anti keeps the rows with no match, and Excel also has Right Outer, Right Anti and Full Outer. Append stacks one table’s rows under another’s, matching the columns by name.
One habit of M catches people out. Text comparisons are case-sensitive, so a filter for “Acme” misses “ACME” and Remove Duplicates keeps both. Merges match the same way. Put the column into one case with Transform → Format → lowercase before those steps, or tick fuzzy matching in the Merge dialog, which can ignore case.
When a formula is enough
Formulas recalculate the moment anything they read changes, and a query changes nothing until it is refreshed. For data typed into the workbook by hand, and for a tidy-up you will only ever do once, formulas are usually the simpler choice.
Excel for Microsoft 365 also has formulas that return whole tables. FILTER and UNIQUE cover much of what a query’s filter and Remove Duplicates do. SORT sorts, TEXTSPLIT splits, XLOOKUP looks things up, VSTACK stacks one range on another, and GROUPBY and PIVOTBY total by category. A sheet built from those stays live, and anyone who reads formulas can follow it.
A query is the better tool when:
- The data arrives from outside, as a file you replace each time.
- The clean-up takes more than a handful of steps and you want them written down in order.
- The shape has to change, such as months unpivoted into rows, or every file in a folder combined into one table (Get Data → From File → From Folder).
- The rows run to hundreds of thousands. A query can load into the Data Model and leave the sheet’s limit of 1,048,576 rows behind.
The two mix well. Let the query do the cleaning, and put your own formulas on another sheet that reads the loaded table. Typing into the loaded table itself is wasted effort, because the next refresh writes it out again.
Things that catch people out
- File paths. A query keeps the full path of its source. Move the file, or send the workbook to a colleague, and refresh fails until the path is changed under Data → Get Data → Data Source Settings.
- Text encoding. Accented names that arrive garbled were read in the wrong encoding. The From Text/CSV dialog has a File Origin box, and 65001: Unicode (UTF-8) suits most files made today.
- Privacy levels. A query that combines two sources may stop and ask how private each one is. Excel uses the answer to decide whether data from one may be sent to the other, which matters when one of them is on the web.
- No memory of last month. A refresh reads the source as it is now. To keep a history, save each month’s file into one folder and query the folder.
Our own product, disclosed
Where our own software comes into this
Since version 1.29.8, Worksheets in Rutba Office has Get & Transform. Data → From Table/Range opens a Power Query Editor on the table or the list round the selected cell, and From Text/CSV reads a .csv, .tsv or .txt file into the workbook as a query, its first row taken as the headings. The editor shows the first hundred rows the query would load, with the total, and builds each step from the column you click: Remove Column, Remove Other Columns, Rename, Data Type, Filter, Sort, Replace Values, Split Column at a comma, tab, space, semicolon or another character, Trim or a change of case, Group By with a count, sum, average, least or most, Keep Top Rows, Remove Duplicates, Remove Blank Rows, Use First Row as Headers and Index Column. Click an applied step to see the table as it stood after it, and a new step goes in after the one picked. The × beside a step takes it away. Close & Load writes the result as a table on a sheet named after the query.
Queries & Connections lists each query with the rows it loaded. From there a query can be refreshed or edited, and deleting one leaves its sheet. Refresh All runs every query and then refreshes the pivot tables, and each refresh is one undo step. Version 1.29.10 added Merge Queries, with Left Outer, Inner, Left Anti and Full Outer joins, and Append Queries, and a query can now read another query’s result, so a CSV can be merged with a table by making a query of each.
What it does not do: there is no M and no Advanced Editor, no custom or conditional column, and no Fill Down or Unpivot. A query reads tables and lists in its own workbook and text files, so other workbooks, folders, databases and web pages are out of reach. Merge has no right-hand joins and no fuzzy matching. A query never refreshes by itself, on opening or on a timer. A query made on a plain list keeps that list’s range, so rows added under it later are left out. Make the list a table first (Home → Format as Table) and the query reads the table by name. Text files are read as UTF-8. Text comparisons ignore case, the opposite of Excel’s, so “Acme” and “ACME” are one value to every step here, merges included. A text date given the Date type is read month first when it is written with slashes, and there is no locale to choose. The queries are kept in the workbook in a part of the suite’s own. Excel keeps that part and opens the loaded tables as tables, but it does not see the queries, and queries made in Excel are neither shown nor refreshed here.
Related
How to automate a spreadsheet with scripts, and run them safely
A script is a short program that does to a workbook what you would otherwise do by hand, in the same order every time. In Excel the current way to write one is Office Scripts, recorded or typed. Before you run any script, and especially one somebody sent you, the question to ask is what it can reach beyond the workbook.
How to sync a desktop calendar and address book over CalDAV and CardDAV
CalDAV and CardDAV are the open protocols many calendar and contact services use, and they let a desktop program keep the same events and the same people as your phone. Setting one up comes down to the server’s address and, on many services, an app password. Here is where to find both, and what happens when two devices change the same thing.
How to do a mail merge: letters, envelopes and labels from a spreadsheet
A mail merge is one document and one list. The document says where each piece of the list goes, and the merge makes a copy for every row. It goes wrong in the same few places every time (dates and money in the wrong format, blank lines where a field was empty, the wrong sheet), and each of them has a plain fix.
Ten apps, one free download
No account, no telemetry, and it works with the network switched off.