Power Query is a game-changer in terms of the amount of time I save, ensuring imported data is ready for analysis in Excel. If you’re new to this tool, it’s helpful to know a few commands that can take your data transformation skills to the next level. Even if you’ve never heard of Power Query, it’s still helpful to look at these examples to see if you’ll be motivated to learn how to use Power Query.
Cleaning data in Excel, especially for multiple workbooks, is one of the most annoying tasks any Excel user has to deal with. But with Power Query, that task can even become somewhat enjoyable.f
Split cells by delimiters
Easily split data into columns
A common problem you’ll run into when cleaning an Excel sheet is when data that should be split into two columns is combined into one. Take a look at the screenshot below, where the Order ID column also has the customer name.
We are going to split this column in Power Query using a delimiter, a character or symbol (like a comma, space, or hyphen) that separates data within a cell. The delimiter is the hyphen or dash (-) separating the order ID from the customer name in the example above.
Let’s assume you have this sheet saved somewhere on your computer. To import it, select the Data tab and click Get Data -> From File -> From Excel Workbook in the Get & Transform Data command group in the ribbon.
From there, navigate to where the file is, select it, and click Import. Under Display Options on the left, select the sheet with the data and click Transform Data. Keep in mind that this is just one way to import data into the editor — the options will be different depending on where you’re getting the data from.
In the Power Query editor, highlight the column (Order ID in our example) and click Split Column -> By Delimiter in the Transform command group of the Home tab. In the Select or enter delimiter dropdown, select Custom. Directly below it, enter the delimiter you want to use and click OK.
Once the column is split, rename the new columns to something that makes sense. I renamed them to Order ID and Customer Name, respectively. Afterward, click Close & Load in the Close command group.
Subtract dates
Get precise date differences
Continuing with the example table above, we can also perform various calculations on the columns in the Power Query editor. I usually take this opportunity to add extra columns for certain calculations. The most common one I do is find the difference between dates.
Here, I want to find how many days the orders were delivered in, so I am going to subtract the order date from the delivery date. To do that, select the Delivery Date column, press Ctrl, and then select the Order Date (mind the order of selection). Next, select the Add Column tab, and click Date -> Subtract Days in the From Date & Time command group of the ribbon.
The new column will have a generic name like Subtraction. You can double-click it and rename it to something more meaningful. From there, you can close and load the sheet into Excel for your data analysis.
Filter for errors
Ensure you’re only seeing the affected rows
Power Query doesn’t have an easy way to filter so that you only show rows with errors. These can be a headache if they’re in a file with hundreds of rows or more.
Suppose you know that the Order Date column has errors, and you want to manually resolve them before loading the data in Excel. You can create a filter to only show the affected rows.
Select the Add Column tab and click Custom Column in the General command group of the ribbon. In the Custom column formula text area, enter the following formula and click OK:
try [Order Date]
Power Query will add a new column called Custom, and the rows will have either a TRUE value if they have errors or FALSE if they don’t. Now, do the following:
- Click the arrow icon on the left of the Custom column
- Uncheck everything except for Has Error.
- Click OK.
- Click the arrow icon next to the Custom column again.
- Uncheck False.
- Click OK.
You will now only see the rows with errors for you to inspect. If the affected rows are not important, the fastest way to deal with them is just to right-click the column and select Remove Errors in the menu. This will delete the rows, so use it carefully.
If they’re important, right-click the column and select Replace Errors. If they all need to contain the same value, enter it in the text box and click OK. If not, and you would still like to keep them but eliminate the errors, assign a value like null so the errors are resolved.
You can also go back to the source, correct the errors, and re-import the data afterward.
Afterward, delete the steps for creating the filter in the Applied Steps section of the Query Settings panel on the right. For me, the steps were Added Custom1, Expanded Custom1, and Filtered Rows1. Just click the X icon next to them and click Delete in the pop-up.
Unpivot your data
Aid data analysis
Pivoted data is that which is structured in a wide format, with values spread across multiple columns. This can make data analysis difficult when using something like a pivot table in Excel.
Take a look at the screenshot of the table below that shows sales data across three states. Since months are a category, they should really be in rows instead of columns to aid data analysis.
To fix this, we need to unpivot the data, meaning it needs to be reshaped into a tall format. Essentially, the states, months, and sales need to be in separate rows.
Doing this manually will involve a lot of copying and pasting, manually aligning values, and ensuring there are no errors. But Power Query can make it easier.
To do that, select the Data tab, highlight the table, and click From Table/Range in the Get & Transform Data command group of the ribbon. In the Power Query editor, select the columns you want to unpivot by holding Ctrl and clicking on them (e.g., January, February, and March). Right-click them and select Unpivot Columns in the menu.
Your data is now unpivoted and ready for further data analysis. Be sure to rename the Attribute and Value headers to something more descriptive. This would be Months and Sales in our case.
A simple query goes a long way
It’s not an exaggeration to say that Power Query will make you look forward to cleaning Excel data. This is especially true since you can create an automated process to clean multiple sheets in the same way. And, as you can see, you can do a lot with a few simple commands.
