1. Navigating the Power Query Editor
Let's get started by opening up an Excel workbook. To open Power Query, click on ‘Data’. Under ‘Get Data’, you’ll see “Launch Power Query Editor”. A handy tip here is to create a shortcut. Right-click here and select “add to quick access toolbar”. A new icon will appear in your toolbar to launch the power query editor in a single click. Let's open it up.
As you can see, there is nothing currently in the editor, so let’s create our first query. Click the “new query” section of the Home ribbon, and under ‘New Source’, select ‘Excel workbook’. Find dailycensus_2230.xlsx and click Import. Next, Power Query asks us which tab we want to load. Select it and click OK.
Now we can see our new query.
Let’s take a moment to overview some of the key areas of the power query editor window.
The ribbon across the top has all guided point-and-click functions, including the “new query” we already used. Other tabs cover transformations, adding columns, and various view functions that will be explored further in this course.
Next, we have the Query List, which lists all queries; Data Preview, which shows the selected query’s contents; and Applied Steps, which is essentially a log of query preparation steps.
Notice how Power Query automatically logs applied steps with a label of what was done. Clicking on any step displays its data preview and the corresponding code that appears here. This is the formula bar, and the coding language is called M-Formula language. It’s essentially the behind-the-scenes code documenting each data transformation step.
Clicking Source shows the workbook that was connected, navigation shows which tab was selected, and in “promoted headers”, power query automatically recognized the descriptive column header for each. Lastly, a data type was automatically identified for each column.
Now, we will go ahead and create another query by loading another dataset. Another way to load a new query is by right-clicking in the queries list and selecting “new Query”.
We’re going to select a text file called drug_administrations. Notice the similarities here in the automated applied steps that are derived.
Power query lets us easily rename columns, for instance, “nurse_id” to “provider_id”.
Changing data types can also be done, for instance, from decimal to integer, so they’re now rounded to the nearest whole.
You can also sort columns easily by selecting arrows at the top right.
Reordering columns can be done by dragging and moving around.
Notice how each change is documented in the applied steps.
You can also undo steps by clicking the “X” on the left. It will return the query to the last applied step.
It is sometimes helpful to rename query steps, particularly for documenting more complex actions. Let's label this one “sort by dose”.
Let's review some of the ‘manage column’ options in the daily census query. Power Query can remove or un-remove selected columns easily. Let's remove ‘hospital_site’. You will discover that these options apply to multiple columns as well, and many of these options can also be done by right-clicking a column.
Now, say we have set up our queries so that we are ready to analyze in our Excel workbook. Clicking Close & Load is the default option, which essentially loads each query as a Table.
A Queries & Connections pane will appear and list the queries we created, with each table on its own tab. Now, these tables can be utilized for analysis just like any Excel table.
During this course, you will progressively build queries based on loading multiple datasets in your own Excel Workbooks. However, should you ever lose progress, or need to check on solutions, we have provided Excel workbooks for you in the Exercises folder. Since these workbooks require external connections to the source datasets, you might need to authenticate or enable the connections by clicking Enable Content and making the file a trusted document. Depending on if you are using Microsoft or Mac OS system, these steps might look different.
Now, it's your turn!
2. Let's practice!