We all occasionally find ourselves navigating the rough terrain of ugly data. Power Query is an excellent tool to have in your back pocket for this eventuality. But here is the deal, Power Query is just that… powerful, and because of this, potentially complicated. In fact, I know a lot of people who have been interested in learning about this beast, only to get quickly overwhelmed in the complexities. So here is my aim with this Byte: a simple introduction to Power Query. Let’s check it out.
What is Power Query?
Power Query is a feature in the PC version of Excel 2016 or later, also known as Get & Transform. There are a multitude of uses for this feature, but it really shines to take on ugly data like this:
… and transform it into something more workable.
Furthermore, since the result is a query that is connected to your data, it can be updated with a simple click of a button.
Exercise File
If you would like to follow along with my steps below, here is an Exercise File:
A Couple Notes:
- You will need the PC desktop version of Excel 2016, 365, or 2019 to use this feature.
- For this Byte I am assuming you have worked with Tables and Pivot Tables in Excel before. If you haven’t, please come to my Excel Essentials and Excel Pivot Tables Sessions and learn about them!
- Needless to say, this is all fictional data.
The Scenario
The scenario is we have some ugly data that we have exported from a different source. The source has thrown lots of data into one column of a spreadsheet. Monthly, we are asked to create a pivot table of fees owed by class and area code, but this will be rough going, given how the data looks:
Format as a Table
I have mentioned before that formatting your data as a table has many advantages, and here is another excellent example. There are many data sources you can use for Power Query, but let’s start here for now.
1. Click on any cell inside the data. Do not preselect the entire column.
2. In the ribbon, select Format as Table, and select any style.
3. Make sure that all the data is encompassed in your range, make sure My table has headers is checked, and press OK.
More details about tables are discussed in the Excel Essentials training, so check out one of those sessions if you have not already.
Create a Query
1. Go to the Data tab, and in the Get & Transform Data group, select From Table/Range.
(note: you can also access this from the Get Data dropdown if it is not readily visible)
2. You will be taken to the Power Query Editor.
This is a little like an alien abduction from your comfortable Excel home… the look and feel are kind of the same as the rest of Excel, but also kind of different. Power Query uses a different type of code than the rest of Excel, so some things will not be intuitive. I don’t want to get too far in the weeds, but for now note that:
- There is no undo, but you can always delete a step on the right,under Applied Steps, where coincidentally, every step of the query will appear forevermore.
- There is a ribbon with Transform and Add Column option. Although options will look similar on both tabs, items on the Transform tab will change an existing column, and items on the Add Column tabwill create a new one
- A lot of options are also accessible on a right click menu.
Transform the Data
In the query, there is only one column with a lot of ugly data, but I notice most of the data is separated by a colon “:”. Let’s begin by splitting up the columns by this delimiter.
1. Click at the top of the column to select all of the data in the column.
2. In the ribbon, or on a right click menu, select Split Column –> By Delimiter.
3. Make sure Colon is selected, and Each ocurrence of the delimiter is selected. Press OK.
Your data should split into separate columns at each occurrence of a colon.
4. Let’s work on splitting the phone number. Select the phone number column, and Split Column –> By Number of Characters.
5. We want to split after 3 characters, Once, as far left as possible. Click OK.
Cool, now we have a separate column for the area code!
6. At the top of each column, double click on the title and give each column a name: Name, Area, Phone, Grade, Class, Fees.
Load the Transformed Data
1. You have the option to name your query on the right side of the screen, under Query Settings –> Properties. If you will be doing multiple queries, this might not be a bad idea.
2. After this, the final step is to press the Close & Load button in the Home tab.
Like magic! Our data loaded to a new tab in a new table.
Good News Part One: Data is Easier to Work With
Okay, this data is going to be so much easier to work with. Remember, my original goal was to make a pivot table showing fees owed by area code. Now that this data is in a table, in a few clicks, I have exactly the information I need. Beautiful!
Good News Part Two: Updating Data
We could have fixed up that data in a variety of other ways in Excel (hello, Flash Fill!), but the especially cool part about Power Query is the ability to refresh data in the future with one click. Remember I mentioned this was a monthly report I had to create… so here is what I would do next month… rather than recreate all the steps to make the data readable again:
1. Load the new data into the source table.
2. Right click on the query table and select Refresh
All the query steps we did in the previous section will happen automatically with my new data.
More Types of Queries
This example involved a query from a table within a document, but you can query tons of different sources: Excel documents in seperate locations, Access databases, Azure databases, online sources… seriously, check out all the options on the Data tab, Get Data dropdown.
Interested in a Power Query Session?
This was a very basic introduction to Power Query. There is a lot more to cover about this amazing feature. WSU friends, I would like to poll the audience here… I think it would be fun to offer a Power Query Session to delve more deeply into its various abilities, but would like to hear from you all if this is something that would interest you. If you are interested, please send me an email!
Congratulations, Power Users!
Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser
Christina Covey
Brandon Whiteside
