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.
- Power Query
- Transforming/Editing Data
- Combining/Merging data
- Extracting File Names and Path Directories
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.
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:
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.
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:|
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:
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:
- Download a new report
- Save in the same folder
- Click Refresh in the Excel file
And ta da! We can see the new data is now in here:
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:
- Select the column we would like to split
- Go to Transform tab at the top
- Select Split Column > By Delimiter
- Select delimiter option: in the example below, we will select Custom and enter “-“
And this will be the end result:
Day of the Week
- Select the column with dates
- Go to Transform tab at the top
- Select Date > Day > Name of Day
And this will be the result:
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.
- Select relevant column
- Deselect the data we want to remove
In this example, let’s say we want a report on session cancellation:
And this will be the result:
Note: this can be used like an IF function
- Go to Add Column tab
- Select Conditional Column
- Apply the relevant conditional formatting
In the example below, we will group all our members into age brackets:
And this is the end result:
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.
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.
- Load the first set of data
- Load the second file
- Right-click on one of the queries in Workbook Queries on the right (see below)
- Select Merge
- Select the second set of data you would like to merge in the second dropdown box (see below)
- In both set of data, select the columns which match and connect the two data sets together
- Click Ok
- Select the columns you would like to merge into the first set of data
- Close & Load
Steps 3 & 4:
Steps 5 to 7
And this will be the new table:
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.
- Go to the Data tab > New Query > From File > From Folder
- Either paste in the folder path or browse
- 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)|
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.