Power Query in Excel — step by step guide

Setting up Power Query in Excel

Power Query lets you pull data from various sources, edit it — combining across sources, splitting or adding columns, filtering — then load it into Excel and build Pivot Tables or charts from it. Like a macro, the steps are recorded so the same query can run again on next month's data, and you can add, remove, or reorder steps whenever you need to.

Querying and loading the data

Start from Data > New Query. You can create a query from a workbook, CSV, text file, XML, or folder:

Using a folder source: go to Data > New Query > From Folder, then paste or browse to the directory:

You'll see the list of files in that folder. This step just combines and loads the data — for editing and transforming, see the next section.

Once loaded, use the filter on the Source.Name column to quickly confirm every file made it in:

From there, Pivot Tables pair naturally with Power Query — both can be refreshed with a single click once set up.

Loading new data

Once a query is built from a folder path, updating it is simple:

• Download the new report
• Save it into the same folder
• Click Refresh in the Excel file

And the new data appears:

Transforming/editing data

Before loading, you can format the data first — the steps are recorded and re-run on refresh, and can be edited afterward. To start editing before loading:

Splitting columns

1. Select the column to split
2. Go to the Transform tab
3. Select Split Column > By Delimiter
4. Choose the delimiter — e.g. Custom, and enter "-"

The end result:

Day of the week

1. Select the column with dates
2. Go to the Transform tab
3. Select Date > Day > Name of Day

The result:

Filtering columns

Removing irrelevant information before loading saves time down the line — select the relevant column, then deselect what you don't need. Here's an example filtering for session cancellations:

The result:

Conditional formatting

This works like an IF function:

1. Go to the Add Column tab
2. Select Conditional Column
3. Apply the condition you need

Example: grouping members into age brackets.

The result:

Editing the applied steps

Unlike editing a macro through VBA, Power Query's recorded steps are easy to edit directly through Query Settings on the right-hand panel — go to View > Query Settings if it's not visible.

You can drag to reorder steps, click the cross to delete one, or open settings to change formatting rules. Be careful when editing: deleting an early step can break later steps that depend on it.

Combining/merging data

You can merge files together as long as they share common data to connect them — e.g. monthly sales reports and a master file of employee or client details.

1. Load the first set of data
2. Load the second file
3. Right-click one of the queries in Workbook Queries
4. Select Merge
5. Select the second dataset to merge in
6. Match the connecting columns in both datasets
7. Click Ok
8. Select the columns to merge into the first dataset
9. Close & Load

The new merged table:

Extracting file names and path directories

The easiest way to extract file names and folder paths is to create a Power Query from the folder itself:

1. Go to Data > New Query > From File > From Folder
2. Paste or browse to the folder path
3. Select Load (don't select Combine)

Once you finish editing, Close & Load gives you a list of every file name in that folder (and its subfolders), along with each file's path.