Power Query for beginners: clean and combine data without formulas

If you repeat the same routine every week (opening several files, copying and pasting, removing empty rows, trimming spaces, fixing data types), Power Query can do all of it for you. You set it up once and, when new data arrives, you just click Refresh.

In this guide you will see what Power Query is, how to create your first query to clean a table, how to join data from several tables and files, and which mistakes beginners usually make.

What Power Query is and where to find it

Power Query is the data import and transformation engine built into Excel. It has been included since Excel 2016 for Windows, in the Get & Transform Data group on the Data tab. In Excel 2010 and 2013 it was installed as a free add-in, and it is also available in Excel for Mac with Microsoft 365, although with fewer data sources.

Its big advantage is that it does not change your original data. It reads the source, applies a series of steps and loads the result into a new sheet. With it you can:

• Clean tables: remove empty rows and duplicates, trim spaces, fix capitalisation.
• Change the structure: split columns, filter rows, rename headers.
• Join data: stack tables with the same columns or match tables on a common field.
• Read every file in a folder in one go.

Your first query: cleaning a table step by step

Imagine you receive this order list with several typical problems: a space before the customer name, an empty row, a duplicated order, a name in lower case and an amount written as text.

1. Click any cell in the data and go to Data › From Table/Range. Excel will ask you to turn the range into a table; accept and confirm it has headers.
2. The Power Query Editor opens. On the right you will see the Applied Steps pane.
3. Go to Home › Remove Rows › Remove Blank Rows to delete the empty row.
4. Select the Customer column and use Transform › Format › Trim to remove the extra spaces. In the same menu, Capitalize Each Word turns “customer c” into “Customer C”.
5. Select all columns (Ctrl+A) and use Home › Remove Rows › Remove Duplicates. Order P-002 now appears only once.
6. In the Amount column, click the type icon next to the header and choose Decimal Number. The value Pending will show as Error.
7. With the Amount column selected, use Home › Remove Rows › Remove Errors or, if you would rather keep that row, Transform › Replace Values before changing the type.
8. Click Home › Close & Load. The result is loaded into a new sheet as a table.

Applied steps: a recorder for your work

Every action you take is recorded in Applied Steps. You can click any step to see what the data looked like at that point, delete a step with the X or change its settings with the gear icon. This makes the cleanup repeatable and easy to review, which does not happen when you clean by hand.

The most useful transformations to start with

Besides the ones above, these are the options you will use most:

• Split Column › By Delimiter: separates names, addresses or codes without formulas.
• Use First Row as Headers: handy with exported files that lack recognisable headers.
• Filter from the header arrow, just like in a sheet, but the filter is saved as a step.
• Replace Values: standardises texts such as “N/A”, “-” or empty cells.
• Group By: builds summaries (total per customer, number of orders) without a PivotTable.

Combining data: append and merge queries

Append: stacking tables with the same columns

If you have January and February sales in two tables with the same headers, create a query for each one (Data › From Table/Range, then Close & Load To › Only Create Connection). Then, in the editor, use Home › Append Queries as New and choose both tables. You will get a single table with all the rows. If a column has a different name in each table, it will appear twice with empty values, so standardise the headers first.

Merge queries: the equivalent of VLOOKUP

Merge Queries matches two tables on a common column. It is what you would do with a formula like this one, which brings the customer name from another sheet:

=VLOOKUP(A2,Customers!A:B,2,FALSE)

In Power Query, select the orders table, use Home › Merge Queries, choose the customers table, select the common column in both and click OK. A new column appears; click the expand icon and pick the fields you want to bring in. Unlike VLOOKUP, it does not break if you insert columns and it stays fast with many rows.

Combining every file in a folder

This is probably the feature that saves the most time. If you receive one file a week with the same structure:

1. Save all the files in the same folder.
2. Go to Data › Get Data › From File › From Folder and select the folder.
3. Click Combine & Transform Data and choose the sample sheet or table.
4. Power Query creates the query with every row from every file and adds a column with the name of each source file.
5. Apply whatever cleanup you need and click Close & Load.

The following week you only have to copy the new file into the folder and refresh.

Refreshing the query when new data arrives

When the source data changes, go to Data › Refresh All or press Ctrl+Alt+F5. Power Query repeats every step on the current data. You can also right-click the results table and choose Refresh to update just that query.

Common beginner mistakes

• Renaming a column in the source: the steps that use it will fail. Keep headers stable or fix the affected step.
• Misread decimals: if the file uses a different decimal separator from your computer, right-click the column and choose Change Type › Using Locale.
• Moving the source folder or file: the query stores the path. Update it from Data › Get Data › Data Source Settings.
• Editing the results table by hand: changes are lost on refresh. Make your corrections inside the query.

When you only need to merge files once

Power Query pays off when the process repeats. If you just need to put a few Excel files together into one without setting up queries, our free tool to merge Excel files does it in the browser: upload the files and download the result. If repeated rows remain afterwards, you can remove the duplicates as a second step.

Conclusion

Power Query turns repetitive cleanup tasks into a process that runs with one click. Start with a simple table, watch how the applied steps are recorded and, once you feel comfortable, try appending tables and reading a whole folder. You do not need to write code to get a lot out of it.

Frequently asked questions