Have you ever pulled in data from various places to restructure it in Excel? Have you found yourself repeating this long, manual process every month when new data is added? The solution is to use Power Query. This blog is the first in a series of four. We will show you what Power Query can do to save you lots of time and help you become more productive. Let’s see how we can clean data in Power Query to improve efficiency (part 1).
What is Power Query and why is it useful?
Power Query is an app which transforms data sourced from many different locations before it is loaded into Excel. It works by making a connection back to the original data source. This means that whenever changes are made, they are saved as ‘Applied Steps’. These are stored in memory. The benefit is that a simple refresh in Excel is all that is needed to update any changes to the original data.
How to use Power Query
- In Excel, open a dataset with a column structure that needs some cleaning. In the example below, the ‘Full Name’ field has text entries where unwanted spaces need to be removed e.g. ‘ Anna Brown’
- Ideally the data needs to be a table before launching Power Query. To create a table, select a cell within the dataset and go to HOME > FORMAT AS TABLE. Select a coloured thumbnail and click OK in the ‘Create Table’ box
- Connect to the source data: DATA > FROM TABLE/RANGE. (NB. For any data that is external to Excel, go to DATA > GET DATA and browse to the specific data source)
- The following window appears:
- Note the ‘Applied Steps’ section on the right that will expand to include further steps
- Now select the ‘Full Name’ field, right click, and go to TRANSFORM > TRIM.
Right click on the ‘Extract Numbers’ field and go to SPLIT COLUMN > BY NUMBER OF CHARACTERS:
- Enter ‘2’ for ‘Number of Characters’ and select the radio button to split: ‘Once, as far right as possible’. Click OK
- Double click on each of the split columns and enter ‘Region’ and ID_no.
Now apply the changes back to Excel:
- In the Home tab, go to CLOSE & LOAD > CLOSE & LOAD TO > TABLE. Click OK to load the table to a new sheet
- Now in a new row under ‘Full Name’ add ‘ Mike Orange’ – with 2 leading spaces and under ‘Extract Numbers add ‘North19’
- Finally in the new sheet, go to QUERY > REFRESH and check the new data has changed
Power Query in Excel can connect to a vast range of external sources as well as within Excel itself. These include other Excel workbooks, financial systems, databases, websites, SharePoint, and many others.
Conclusion
Power Query is a powerful and extremely useful tool. It allows you to repeat routine cleaning tasks on your datasets without having to do it all manually. This tool will therefore help you become more efficient in the way you manage data which ultimately increases profitability. This concludes ‘Clean data in Power Query to improve efficiency (part 1)’.
For course details on our Power Query course at STL, please click on the link below:
https://www.stl-training.co.uk/syl/300/excel-power-query.html
Check out this useful article on data trends:
https://www.inc.com/john-hall/5-trends-in-data-emerging-in-2020.html