Skills level: Advanced
Power Query is by far one of my favourite excel features. I am sure that you have dealt with messy data reports that just make your life difficult. It wastes so much time to rearrange columns, rows or cells, deal with blank entries, empty spaces, duplicates, unnecessary columns, messy formats… You name it, we have all seen it.
Power query is there to save the day. Literally… If you know how to use power query, it will save so much time, you will have days left over at the end of the month with nothing to do!
Power query has a number of functions that you can apply to your data which is specifically designed to clean up and transform your data. It deals with the typical problems that you are faced with when working with all sorts of reports with different layouts and formats. It then stores or remembers the steps that you have taken, and re-apply them when new data is added to your report. It almost works like a macro would, but you can use power query without having to learn any coding.
It is really a powerful tool, and deals with those boring repetitive tasks, that you perform week after week, month after month, so that you can concentrate on the more important things in your career or in your life.
If I start writing about everything that it can do, it will take days, so let’s do an example to show some of its functionality.
Please note, if you work in Excel 2010 or Excel 2013, you may want to download power query first. Click here for instructions on how to download and install power query. If you work in Excel 2016, this feature is already built-in and can be found in the Data tab in the excel ribbon in the Get & Transform group.
In this example I will show you how you can clean up a report and get it pivot ready. You may often come across data that needs to be manipulated so that certain information can be extracted from it, but if it is not in a pivot-ready format, it can take hours to edit the data, before you even begin to analyse.
So here is an example of a type of report that I use on a monthly basis. This is the format that I find it in after exporting:
You will agree that it is untidy, and not in a format that is useful for anything - it needs some TLC before you can use it for analysis.
Let’s see how power query can be used to clean this report up and get the data in a table, ready to be used in a pivot table.
On a brand new worksheet, start a new query (I am working in Excel 2016, it will look slightly different if you work in a different version of Excel): Data > New Query > From File > From Workbook > Navigate to the file that you want to clean up, select Import, click on the file name (a preview will be displayed on the right), and select Edit.
Your file is now in power query, waiting to be cleaned.
You will immediately notice that there is quite of lot of empty space in this report.
Let’s start by getting rid of column 1 to 4. It does not contain any useful data. The Bank (ABC Bank) in Column A is not necessary since the data relates only to ABC Bank. We can also get rid of column 7 – it is also empty.
Click and hold the Control key while you select these columns, right click on one of these column headings and select, “Remove Columns”.
See how the steps are added to the “Applied Steps” section on the right.
Next, I want to get rid of the empty space at the top of the report, as well as the blank row beneath the report headings. The columns containing Client Name, Region and Account number seem to have most cells completed. Select either one of these, select the drop down arrow next to the column name, and filter out the ‘Null’ and ‘Blank’ values.
I have filtered out the NULL values and blank cells separately so that you can see the difference, but you can do it all at once.
Next, you might want to make the first row of data your column headings. Click on the Transform tab, and select “Use First Row as Headers”. This will save you some time to rename all of the columns.
Now, if you want to use the data in a pivot table, the values in the first and second column, need to be copied down to the rest of the cells. In Power Query, you can easily do this by filtering the values downwards. You can select both columns at the same time, right click on any one of their headers, select “Fill” from the dropdown list, and then “Down”, and see how Power Query copies the values into the rest of the cells:
The account numbers you may have noticed, are not uniformly formatted. Some are formatted as numbers, while others are formatted as text (because there are spaces contained in some of numbers).
Get rid of these unwanted spaces easily by using the trim function. First, however, you need to change the formatting to number (or decimal). In the Home tab, select Data Type > Whole number. Then in the Transform tab, select Format > Trim. This removes any spaces between the numbers in the cells. The formatting automatically changes to text again. In this case, we will not need the account number in a number format, since you don’t have to do any calculations with it, so the Text format is fine.
In some of the last columns, there are some blank cells. I rather want a zero value displayed here, so I am going to use the Replace function to do so. Once again, all 3 columns can be selected at the same time (hold the Control key in while you select the columns one by one). In the Transform tab, select “Replace Values”. I’ll leave the “Value to find” box empty, since that is exactly what I want to replace, and then in the “Replace with” box, I enter a zero. Click okay to have the empty cells replaced.
I also want the Funds Available column and Credit Limit column to be formatted as whole numbers:
You may also have noticed that there is a slight variation in the Client Segmentation column.
This will create a problem when you attempt to create pivot tables with these inputs. It will not group together - instead, it will be seen as separate types of entries. In order to group them together, you have to change the one or the other before creating a pivot table. Once again, you can use the replace function:
Note that the replace function is case sensitive. You will have to make sure the “Value to find” is typed in exactly as it appears in your spreadsheet.
The report is now all cleaned up and ready to use. If you are happy with the changes that you have made, you can close and load your query. If you have forgotten to make certain changes, you can easily come back to the query and edit it at a later stage.
This example just touches some of the basic functions that you can perform with Power Query. There is so much more that you can do with it. I encourage you to try it out, and see how it can simplify your work and make your life easier.
Hi, and welcome to my blog :) I am an Excel enthusiast and want to help others keen on improving their own Excel skills. I hope you learn something useful on my blog.