Power Query is possibly the most exciting new Excel feature of its generation… but you might never know it considering how hidden it is from the Excel interface! If you’re looking to connect a data source to Power Query, check out this post:
If you’ve already loaded the data into Power Query and are wondering what the heck to do next, here’s that post: a tour of the Power Query editor.
You can find a completed copy of the workbook here.
Your data is loaded… now what?
When you download the starter file, you will see a penguins table that needs to get loaded into Power Query. To do that, click anywhere in the table and then select Data > From Table/Range. If you need help here, check out this post on Power Query.
Once the data’s loaded, you will see the Power Query Editor menu open and something like this:
Interesting, right? It kinda looks like Excel, but also not. So what are we supposed to do now?
Let’s walk through each element to understand what you’re dealing with.
The Ribbon tab
On the top of the menu (labeled “Power Query Editor” in case you really weren’t sure), you’ll see the familiar ribbon interface:
You should see four tabs on the ribbon here, which are:
Home
Just like with regular Excel, the Home tab has the most fundamental operations in Power Query. But rather than see options to change font size or color here, for examples, most of these tasks are about data cleaning and transformation: choosing rows, deleting columns, merging and more.
Transform
But wait, there’s more! The Transform tab provides even more options for data cleaning and transformation. Notice how many more menu options there are in Power Query than regular Excel specifically for cleaning data. We’ll play around with these options here in a moment.
Add Column
This is really another column specifically for data transformation, right? In particular, it’s to create new columns: whether that’s from a calculation, by using Excel’s pattern-matching algorithm or something else.
View
Finally, the View tab allows us to customize what we see in the Power Query Editor. As I highlighted in this post, these features allow us some nifty data profiling possibilities. You can learn more about those features at this post:
For now, simply click on “Formula Bar” under the Layout group. You will see a formula bar appear on top of your dataset, similar to Excel that you can easily expand with the dropdown icon:
The Formula Bar
If you’re following along with the demo, you should see something like this in the formula bar:
= Table.TransformColumnTypes(Source,{{"species", type text}, {"island", type text}, {"bill_length_mm", type number}, {"bill_depth_mm", type number}, {"flipper_length_mm", Int64.Type}, {"body_mass_g", Int64.Type}, {"sex", type text}, {"year", Int64.Type}})Doesn’t look like your typical Excel function, right? That’s because it’s not… it’s the M programming language used only in Power Query.
Does that mean you need to learn how to code in Power Query? Nope! Using the home ribbon and other features, you can accomplish nearly all your day-to-day tasks without dipping into M at all. However, as you make changes to your query you will see the formula in this bar change, which you are able to edit as necessary.
Having the entire query expressed as code is great because it’s easy to track exactly what is happening, when and repeat the process. Having this code generated is great so you don’t have to write it.
Query properties
OK, let’s move from the top of the Editor and over to the left:
At the top-left of the Editor you will see a list of queries available to you. Right now we’ve only imported one data source into Power Query, but if you had more they’d be listed here. Keep in mind this source data can be everything from a csv file to a database to a web page.
Then, wayy toward the bottom-left you will see the number of rows and columns in your current query. There is also a rather cryptic message about “Column profiling” which will make more sense if you read the data profiling blog post 😼.
The imported data
OK, now let’s get to the part that takes up most of the Editor’s real estate, the part you’re used to working for in Excel: the data!
Go ahead and play around with the data here in Power Query: hide some columns, drop a formula into a cell or something like that.
Spoiler: you can’t.
The Query Settings
Power Query has this data locked down tight! No more random hard-coding formulas or hiding columns. Everything must be done programatically.
Take, for example, something as simple as deleting a column. Let’s drop the year column from our data. Right-click on the column label, then select Remove.
The column is gone from our dataset for good! Or, is it? To see how changes to the data work in Power Query, check out the “Applied Steps” menu over to the right.
The Applied Steps menu
We have a record of the deletion! Every step we take in Power Query will be maintained in the Applied Steps, shown in the box toward the right of your data:
You’ll see this is actually the third Applied Step:
- The first step is to connect to the data. That’s the Source.
- The next step is to set data types. Unlike Excel, in Power Query every value in a column must be of the same type.
If you click on any of the steps, you can “go back in time” to how the data looked at that point:
You’ll also see the M code that was used to generate that step in the Formula Bar. To see the entire script in one place, go to the View tab of the ribbon and Advanced Editor:
Let’s see what happens when we do more to the data: we’ll add an index column.
Go over to the Add Column tab on the ribbon, then Index Column > From 1:
You should now see this extra step in Applied Steps.
Not only can you step in and out of these steps in the menu, you can even delete them. Let’s say for example we want to get the year column back in our dataset. Simply click the X to the left of Removed Columns:
However as the warning message says, once a step has been deleted there is no way to undo that delete.
Exiting the Query editor
This is all pretty cool, right? Feeling a little more comfortable with the Power Query editor? Now let’s try making our exit.
If you’re in the Power Query editor and try going back to regular Excel, you’ll notice you are more or less locked out. The spinning blue wheel prevents you from making any changes.
To exit the query and bring your prepared data into Excel for further analysis, you must close and load the query. To do this, click Close & Load on the Home tab of the ribbon. You should see a menu like this:
You’ve got a few options for what to do with the results of your query:
- First, you have the option to load the data into either a table, PivotTable “Report” (it’s just a PivotTable), PivotChart, or connection only. If you select the last option, the results of your query will not be loaded to Excel but the query will be available in the workbook.
- If you select to load the data to your workbook, you can place it in a new or existing worksheet, similar to inserting a PivotTable
- You also have the option to add the data to the Data Model. This enters Power Pivot and DAX territory and is beyond the scope of this post.
The default loading option is to a table in a new worksheet; let’s go with that. You should now see a second worksheet in your workbook containing an orange-colored table. Neat!
Organizing your worksheets
It can be a little confusing that you now have two worksheets of the penguins table: one is the original source file and the other has been modified via Power Query. By default, you’ll see Excel does use different color tables to signify each kind. It’s also a good idea to give each worksheet a name to indicate whether it’s the input or output data source, such as penguins-in and penguins-out.
Keep in mind too that Power Query does not just work on worksheets from inside the same workbook. You can connect to CSV files, databases, the web and more.
Returning to the Power Query Editor
Missing Power Query? I thought so. If you want to return to your query to continue transforming the data, head to the Data tab on the main Excel ribbon, then Queries and Connections. You should see a menu to the right pop up listing your penguins query. Right click on it and select Edit to be returned to the Power Query Editor:
The Excel you never knew
If you haven’t been using Power Query yet, I don’t blame you… it wasn’t the easiest to find, right? But I hope this post got you acclimated to the Query Editor and what this fantastic tool can do for you.
What challenges are you thinking of solving with Power Query? What questions do you have about the Editor interface? Let me know in the comments. If you are looking to introduce Power Query to your team, please get in touch for corporate training.
Leave a Reply