Excel Power Query: How to Clean and Combine Data

You can fix a large table by hand, one row at a time. Or you can load it into Power Query, define once what needs cleaning and correcting, and from then on get a tidy table every time you click "Refresh". The difference is most obvious when the same report comes back every week: leads from several channels, an order export from your online shop, a stock file from a supplier or a list of invoices from your accounting software.

Power Query is a tool built into Excel for loading, cleaning and combining data. It requires no programming, never changes your source files, and records every action as a step you can repeat or undo.

In this guide you will learn:

• what Power Query is and what it can do;
• where to find it in current versions of Excel;
• how to clean a table step by step, using a product catalogue as an example;
• how to combine files and merge tables;
• how to load the result and keep it refreshed;
• what to watch out for with international data (encodings, dates, phone numbers, personal data), and when it is time to move to full automation.

What Power Query is and why it saves hours

Power Query is a data preparation engine built into Excel and Power BI. With it you connect to data sources in different formats, bring the information together in one place, shape it into the right structure and pass it on: to a worksheet, a PivotTable or a data model.

In the data world this process is called ETL, short for Extract, Transform, Load. Power Query is a complete ETL tool, but it is designed for business users rather than developers. Instead of writing code, you click buttons in the ribbon, and the tool records each step for you in its own formula language, called M.

The main advantage is repeatability. Say that every Monday you download a lead export, delete the columns you do not need, fix the phone numbers and remove duplicates. In Power Query you do this once. Next week you replace the source file, click "Refresh All", and every step runs again in a few seconds.

The second advantage is volume. An Excel worksheet is limited to 1,048,576 rows, but Power Query can process much larger sources, filter and summarise them, and load only the result into the sheet. When you need to work with every row, you load the data into the Excel Data Model, which is not bound by the worksheet row limit.

What Power Query can do

Working in Power Query is easiest to think of in three stages: connect to a source, transform the data and load the result.

Connecting to data sources

Power Query connects to almost every format a business deals with day to day:

• Excel, CSV, TXT, JSON and XML files, plus PDF (tables inside PDF documents);
• web pages that contain tables, and API endpoints that return JSON;
• databases such as SQL Server, Access, MySQL and PostgreSQL (some connections require a driver to be installed);
• files stored in SharePoint and OneDrive;
• an entire folder of files, for example all the monthly reports saved in the same place.

You can connect to several sources at once and combine them. For example, you can take the order list from your online shop and add the cost prices from your supplier's file.

Transforming and cleaning

Once the data is in the editor, you can:

• change data types, for example from number to text or from text to date;
• remove extra spaces, blank rows, unnecessary columns and duplicates;
• change text to upper case, lower case or capitalise each word;
• sort and filter, reorder columns and rename them;
• split a column by a delimiter, or merge several columns into one text value;
• add calculated columns, such as a price including VAT or the number of days since an order;
• group and summarise, for example total sales by month and channel;
• look up values from another table, much like the VLOOKUP and XLOOKUP functions.

All of these changes happen inside Power Query. The source files stay exactly as they were, so there is no risk of damaging the original data.

Loading

You can load the result as a table on a worksheet, build a PivotTable or PivotChart from it, or add it to the Data Model and create reports that combine several tables.

Where to find Power Query in Excel

In current versions of Excel, Power Query needs no separate installation. In Excel 2010 and 2013 it was installed as an add-in, but those versions, as well as Office 2016 and 2019, no longer receive security updates from Microsoft. If your team still works on them, that alone is a good reason to move to Microsoft 365 or Office 2024.

Excel for Windows

This is the version with the full feature set. On the Data tab there is a group called Get & Transform Data, which contains the Get Data button. From there you choose the type of source, and the Power Query editor opens. If your Excel interface is in another language, the names are translated, but the buttons sit in the same place.

Excel for Mac

Microsoft 365 for Mac now includes the Power Query editor, so you can load files, clean data and refresh queries. The range of connectors is narrower than on Windows, so if you depend on a particular database or a specialist connector, check in advance that it is available.

Excel for the web and Power BI

In Excel for the web you can refresh some of the queries built in the desktop app, but building complex queries is still easier in the desktop version. Power BI Desktop uses the same Power Query engine, so what you learn in Excel transfers almost one to one when you start building dashboards.

Practical example: cleaning a product catalogue

Let's take an example anyone who runs an online shop will recognise: an Excel file from a supplier with a product catalogue. It has a SKU column, a product name and a category, and it arrives messy: the leading zeros have disappeared from the SKUs, the categories are all in lower case, and there are stray spaces in front of the product names.

Step 1. Load the file

On the Data tab, click Get Data, choose From File, then From Excel Workbook, and select the file. The Navigator window opens, where you tick the sheet or table you want to load. Instead of Load, click Transform Data, and the Power Query editor opens.

Step 2. Fix the data types

Power Query detects data types automatically, and sometimes gets them wrong. A SKU such as 004512 is detected as a number, and the leading zeros vanish. Exactly the same problem hits phone numbers stored without a country code: 07700900123 becomes 7700900123.

To fix it, click the icon to the left of the column name (a numeric column shows "123") and choose Text. It is best to do this before any other step, because once the zeros are gone they are hard to recover. If needed, delete the automatic Changed Type step the tool added and set the types yourself.

Step 3. Change the case of text

The category column is all in lower case, for example accessories. Right-click the column header, choose Transform, then Capitalize Each Word, and every word now starts with a capital letter. The same menu also offers UPPERCASE and lowercase, which help when the same value is spelled differently across exports.

Step 4. Remove extra spaces and characters

Right-click the product name column, then Transform and Trim. This removes spaces at the beginning and end of the text. The Clean option in the same menu removes invisible characters, which often sneak in when data is copied from websites or older systems.

Step 5. Remove duplicates and filter

To remove duplicate rows, select the SKU column and choose Remove Rows, then Remove Duplicates. You can also filter out products that are out of stock, sort by category and delete the columns your website does not need.

Step 6. Review the recorded steps

On the right-hand side of the editor there is a list called Applied Steps. Every action you took has been recorded there as a step. Clicking a step shows the table as it looked at that moment, and right-clicking lets you delete or rename it. This is what makes Power Query so easy to audit: you can see exactly what was done to the data, and in what order.

Combining files and merging tables

The real power of Power Query shows up when you have more than one file. The two key operations here are Append and Merge.

Combining files from a folder (Append)

Suppose that every month you download an order export from your online shop. Instead of copying and pasting them all into one sheet, save all the files in the same folder and connect to it via Get Data, From File, then From Folder. Power Query offers to combine the files and stacks them into a single table. Next month you drop a new file into the folder, click Refresh, and it appears in the report.

There is one condition: all the files must have the same structure, with the same column names. If the export format changed halfway through the year, the refresh will fail or produce empty columns.

Merging tables (Merge)

Merge Queries joins two tables on a shared column, just like VLOOKUP, but without formulas that break. For example:

• a table of leads from your website form and a table of deals from your CRM, joined by email address;
• an orders table and a supplier cost table, joined by SKU, to calculate gross margin;
• an ad spend report by campaign and a leads report by campaign, to calculate cost per lead.

In the merge window you pick the two tables, select the key column in each and choose a join kind. The most common is Left Outer: all rows from the first table, with the matching data from the second next to them. You then expand the new column and choose which fields to bring in.

Other useful operations

• Unpivot Columns turns a "wide" table, with one column per month, into a "long" table that is easy to summarise and chart;
• Group By summarises data by category, for example sales by channel;
• Fill Down fills empty cells with the value from the row above, a common problem in reports exported from accounting systems;
• Column From Examples lets you type a few examples of the result you want, and Power Query works out the formula for you.

Loading the result and refreshing the report

When the table is ready, click Close & Load to load it to a new sheet, or Close & Load To to choose a different destination:

• Table – a regular table on a worksheet that updates on refresh;
• PivotTable Report – a PivotTable based directly on the query;
• Only Create Connection – the query is saved without loading any data to a sheet. Useful for helper tables used only for merging;
• Add this data to the Data Model – loads the data into the Data Model, where you can relate tables and work with more rows than a worksheet allows.

How to refresh

On the Data tab, click Refresh All, and every query in the workbook runs again. In Query Properties you can set the query to refresh automatically when the file opens, or every few minutes while it is open.

If you need a report that updates even when nobody opens the file, that is a job for a different tool: Power BI with scheduled refresh, or an automation workflow that pulls the data itself. We cover the difference between Excel reports and dashboards in our guide What is Business Intelligence, and how to pick the right chart for the data you have prepared in our guide to chart types.

Power Query in a growing business: scenarios and tips

Common scenarios

• Weekly lead report. Exports from your website form, Meta Lead Ads and Google Ads are combined into one table with a source column, normalised phone numbers and no duplicates.
• Matching orders and payments. An order export from your shop (WooCommerce, Shopify or Webflow Ecommerce) is merged with a payout report from your payment provider by order number, so you can see what has not been paid or not been shipped.
• Revenue reports. Accounting and invoicing tools usually let you export to Excel or CSV. Power Query summarises the data by month, client or type of service, and adds columns for net and gross amounts including VAT.
• Supplier stock and prices. Supplier files arriving in different layouts are brought into one consistent structure before you update the catalogue on your website.

Encodings, dates and number formats

• CSV encoding. A CSV file that shows garbled characters, such as accented letters turning into symbols, was usually saved in a different encoding from the one the tool guessed. The file import window has a File Origin field: try 65001: Unicode (UTF-8) first, and for older files a Windows code page such as 1252: Western European (Windows).
• Dates. US systems export dates as month/day/year, while most of Europe uses day/month/year. To stop 3 February from turning into 2 March, use Change Type, then Using Locale on the column, and set the locale of the source.
• Decimal separators. Many European systems write 1.234,56 where others write 1,234.56. The same Using Locale option tells Power Query how to read numbers correctly, so amounts are not misread as text or multiplied by a thousand.
• Phone numbers. Set the column to Text, remove dashes and spaces with Replace Values, and if you need the international format, add a column that replaces the leading zero with the relevant country code.
• Column names. Names in any language work fine, but if the query will also feed other tools, such as n8n or a database, short English names without spaces are easier to work with.

Privacy and data security

Lead and customer files contain personal data. If you work with people in the EU, the GDPR applies to how you store and process these files, including principles such as data minimisation and appropriate security. This is general information, not legal advice. In practice: do not keep full exports on your desktop and do not send them around by email or messaging apps. Store them in a shared folder with proper permissions, and remove columns the report does not need, such as ID numbers or full home addresses, in the transformation step.

Common mistakes and when to move to another tool

Editing the source file instead of the query

Power Query is designed so that the source stays free of manual edits. If you fix the export file by hand before loading it, the fix will be gone next time. Every correction should be a step in the query.

Trusting automatic type detection

The automatically added Changed Type step causes most problems: vanished zeros, swapped day and month, and numbers detected as text. Check it in every new query.

Changing the export format without updating the query

If someone renames a column in the CRM or adds a field to a form, the query may fail on refresh. Document where each source comes from and who is responsible for it.

Building everything in one long query

Dozens of steps in a single query are hard to check. It is better to split the work: one query per source, one query that combines them, and one that summarises the data for the report.

When Power Query is no longer enough

Power Query is excellent when one person prepares a report on a regular schedule. But there are signs that it is time for the next step:

• data needs to move between systems in real time, for example a website lead that goes straight into the CRM and triggers a WhatsApp message;
• the report has to update without anyone opening Excel;
• several people work on the same data at the same time;
• the files are growing to sizes Excel struggles with.

At that point you move to automation with n8n, Make or Zapier, to direct API connections between systems, or to a database with a dashboard. We explain what this kind of automation can do in our guide to business automation with n8n. The logic you designed in Power Query, which columns to clean and how to join tables, is exactly the specification an automated workflow needs.

How RJL Studio helps you get your business data in order

At RJL Studio we connect your website, forms, CRM, payment systems and ad platforms, so your data ends up in one place without copy and paste. When a well-built Excel report is enough, we help you set it up properly. When you need more, we build automated workflows in n8n, Make and Zapier, along with API integrations that send leads, orders and campaign data straight into your systems.

Ready to stop losing hours to manual reports? Read about our automation and integration services or get in touch and we will get back to you.

Frequently asked questions

Does Power Query cost anything?

No. Power Query is built into Excel in Microsoft 365 and current Office versions, as well as the free Power BI Desktop. You do not need to buy an add-in or a separate licence.

What is the difference between Power Query and Power Pivot?

Power Query prepares the data: it loads, cleans and combines it. Power Pivot is the Excel Data Model, where you create relationships between tables and build calculations for reports. They usually work together: Power Query prepares the tables and loads them into Power Pivot.

Do I need to know how to code to use Power Query?

No. Most of the work is done by clicking through menus, and the tool writes the M code for you. If you want, you can open the Advanced Editor and edit the code directly, but it is not required.

Does Power Query work with Google Sheets?

Excel has no direct connector for Google Sheets. You can download the sheet as an Excel or CSV file and load that, but for ongoing work between Google Sheets and Excel or a CRM, an automation tool such as n8n, Make or Zapier is more convenient.

Which is better: Power Query or automation?

It depends on the task. For a report that one person prepares weekly or monthly, Power Query is more than enough. When data has to flow between systems continuously, trigger actions such as notifying a sales rep or updating the CRM, or update without anyone touching it, automation is the better choice.