Power Query in Excel – Step by Step Guide

Can you use Excel without power query? Yes. But can you use Power Query in Excel to simplify and automate any formatting and reporting processes that you do every week or every month? Answer is also yes. It is definitely worth spending time setting up Power Query.

We will first go through a simple illustration of how Power Query works from start to end. And then we will go through various editing/transformations we can do before loading the data.

Setting Up Power Query in Excel

Power Query allows us to query data from various sources, edit the data (e.g. combining data across multiple sources, splitting/adding columns, filtering), load the data onto Excel and create Pivot Table/Charts from it. Similar to Macro, the series of steps is recorded in the query and we can run the query again with new data (for example when next month’s report). We can also add/remove/rearrange/edit the steps easily if required.

power-query-excel-from-folder

Querying and Loading the Data

We can begin Power Query by choosing the relevant data source. Go to Data > New Query. We can create a new query from various sources such as workbook, CSV, text file, XML or Folder:

Which file should I choose?
It is important to pick the right file here. If we are going to add new data files on a regular basis, it will be best to create a folder and save all the files there. See image on the left:

In this case, we should create a new query from Folder. When we add new files into the folder, it can be updated easily by refreshing the data.

If we are working with one data file and that one data file gets updated regularly, then we should create a new query from workbook or CSV depending on the file type. Note: make sure you first check if your file is an Excel workbook or a CSV.

In this case, we will use the example above and create a new query from Folder. Go to Data > New Query > From Folder. Here we can copy and paste the directory path or you can browse for the folder:

power-query-excel-directory-path

After we click Ok, we can then see the list of files in that folder. In this section, we will not edit/transform the data. For information how to edit/transform data, please go to next section – Transforming Data. Here we will simply combine & load the data.

power-query-excel-combine-load

A preview of the data in your files will appear.

Before we load the data, it is important to make sure all files have the same headings. Note: the least editing we do to the raw data reports the better. It is better and easier in the long run to make changes to the data here in Power Query than to make changes to the raw data reports every time we have a new report. The headings we have here will be the headings of all the data when they are loaded:

power-query-headings-excel

Click Ok. And Excel will load the data into a table format. Quickest way to make sure the data has loaded correctly is to use the filter in Source.Name column and check that all data file names are there:

power-query-source-name

Once we are happy with the data in the table, we can create Pivot Tables with the relevant information. In this section, Pivot Tables and Power Query make a great combination. Because same as Power Query, once Pivot Tables are set up, they can be updated and refreshed easily with click of a button.

Loading New Data

Now that we have created a query from the folder path, it is very easy to update new data. All we have to do is:

  1. Download a new report
  2. Save in the same folder
  3. Click Refresh in the Excel file

power-query-excel-refresh-button

And ta da! We can see the new data is now in here:

power-query-excel-refresh-new-data

Transforming/Editing Data

Prior to loading the data, we have the option to format the data first. Similar to Macro, the series of steps is recorded and will run again when query is refreshed. The series of steps can also be edited. To edit the data before loading:

power-query-excel-combine-transform

Splitting Columns

  1. Select the column we would like to split
  2. Go to Transform tab at the top
  3. Select Split Column > By Delimiter
  4. Select delimiter option: in the example below, we will select Custom and enter “-“

power-query-excel-split-column

power-query-delimiter-custom

And this will be the end result:

power-query-delimiter-result

Day of the Week

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

power-query-excel-day-of-week

And this will be the result:

power-query-transform-day-of-week-result

Filtering Columns

Simple filtering can be very helpful in removing irrelevant information before loading the data. Don’t forget: the more we can automate here the more time we will save in the future.

  1. Select relevant column
  2. Deselect the data we want to remove

In this example, let’s say we want a report on session cancellation:

power-query-filtering

And this will be the result:

power-query-filtering-result

Conditional Formatting

Note: this can be used like an IF function

  1. Go to Add Column tab
  2. Select Conditional Column
  3. Apply the relevant conditional formatting

In the example below, we will group all our members into age brackets:

power-query-conditional-formatting

And this is the end result:

power-query-conditional-formatting-results

Editing the Applied Steps

To edit Macro, we would have to go into VBA and change the codes. But with Power Query, it is a lot easier to edit the recorded steps. See the Query Settings on the right hand side. If you accidentally removed it, you can go to View at the top and select Query Settings to bring it back.

Here you can drag to reorder the steps, click on the cross to delete or click on settings to change the formatting rules.

Of course you should be careful when editing the steps. If you added a new column and then reordered it or if you used that new column to add more columns, you will get an error message if you try to delete that first column you added because it affects the subsequent steps.

power-query-editing-steps

Combining/Merging Data

We can merge merge files together as long as the files have common data that can connect them together. For example we could have monthly sales reports and also a master file on employee/client details.

  1. Load the first set of data
  2. Load the second file
  3. Right-click on one of the queries in Workbook Queries on the right (see below)
  4. Select Merge
  5. Select the second set of data you would like to merge in the second dropdown box (see below)
  6. In both set of data, select the columns which match and connect the two data sets together
  7. Click Ok
  8. Select the columns you would like to merge into the first set of data
  9. Close & Load

Steps 3 & 4:

power-query-merge

Steps 5 to 7

power-query-merge-data

Step 8

power-query-select-data

And this will be the new table:

power-query-merged-data

Extracting File Names and Path Directories

An easy way to extract file names and folder path directories within a folder is to create a Power Query from the folder.

  1. Go to the Data tab > New Query > From File > From Folder
  2. Either paste in the folder path or browse
  3. Select Load (Note: make sure we don’t select Combine here)
Note: if there are lots of files and subfolders, we can select Edit and filter out information we don’t need. For example in the extension column, we can filter to only load csv and xls files)

power-query-load-paths

power-query-filter-extensions

Once we finish editing, click on Close & Load and we will have a list of every file names within that folder (and its subfolders) and also the folder path directory each file is in.