Once Power Query is installed, it has its own ribbon in Excel 2010 and 2013. It is part of the Data ribbon in Excel 2016.
Navigate to the CSV file and click OK. This will open the Power Query window, as per Figure 4.
We will perform three data-cleansing tasks on the file.
1. Remove the top three rows.
Click the Remove Rows drop down and choose Remove Top Rows, as per Figure 5.
Enter 3 and click OK.
Then click the Use First Row As Headers button. This instructs the Query to treat the first row of the revised layout as column headings.
2. Correct the Date column.
Right click the Date column, choose Change Type and then the Date option. See Figure 6.
This has converted a date Excel can’t recognise into one that it can.
3. Correct the Amount column.
The numbers in the Amount column are left aligned. This usually means they are formatted as text.
Ensure they are imported as values by changing their type.
Right click the Amount column, choose Change Type and choose Currency. See Figure 7.
All these changes have been captured in the Query Settings on the right side of the Query window. See Figure 8.
The file name is automatically used as the Query name, but you can change it if you need to.
Even though we changed the type of two columns, it only lists one change.
The Query typically combines column type changes into one step. You can click the small gear icon on the right of an Applied Step to amend the step without having to redo it.
Power Query works a bit like Excel’s Macro Recorder, which captures a sequence of processes.
These Applied Steps will be repeated each time the Excel file is refreshed.
NO UNDO COMMAND
One thing you need to know about the Power Query window is that there is no Undo command.
Typically, if you try something and it doesn’t work, you point to the Applied Step on the right side, delete it and start again.
Professional Development:
Business intelligence using Excel and Power BI Desktop 2018 ‐ 2 day workshop: day 1 of this workshop introduces Power Pivot, the Data Model and the DAX formula language. Day 2 covers Power Query, Power View and Power BI Desktop.
This returns the data to how it was before the step was applied.
The completed Query window is shown in Figure 9.
To create the table in your file, click the Close & Load icon on the far left side.
This will create a formatted table of the data from the CSV file in a new sheet. See Figure 10.
This table is ready to use with pivot tables or formula-based reports.
Note the leading zeroes have been retained in the Account column.
When you open a CSV file in Excel, you typically lose any leading zeroes. Power Query avoids that issue.
This technique works well, because to update your data, all you need to do is replace the CSV file with the latest data, being careful to retain its name.
You don’t even need to open the CSV file; you open the Excel file and click Refresh All in the Data ribbon tab and that will automatically update the data, apply the data-cleansing steps and include any extra rows.
Some of Power Query’s other data-cleansing abilities include:
- removing unwanted columns
- filling in blank cells with data from above
- correcting layout issues
- splitting columns
- converting US dates to Australian dates
Power Query works just as well with .txt files and it can import data from most databases.
This has demonstrated the basics of Power Query, but it can also handle large data sets.
In the past, you needed to use formulas or macros to perform these types of data-cleansing exercises, but Power Query makes automating data cleansing much easier.
The companion video and an Excel file may assist your understanding.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to a4@iinet.net.au