1. Introduction to Power Query demo
In the first demo for this course we will introduce you to Power Query and how to do some basic data transformations.
Firstly, to access the Power Query editor, we navigate to the Transform Data option in the home ribbon of Power BI desktop.
There are many features in Power Query that help us prepare our data by transforming it to suit our needs. None of them really work unless we have some data first, so let’s import some!
When we click on “New Source”, we will be greeted with all the various data connectors that Power BI has. This library of sources is ever-growing and you can even get some custom connectors from the Power BI marketplace if you need to connect to a data source that isn’t listed here.
We’re going to connect to a web data source, so let’s select the “Web” connector and enter the URL of the website that hosts our dataset.
https://www.bankrate.com/retirement/best-and-worst-states-for-retirement/
Since this content is freely available for anyone with an internet connection, we don’t need to enter any special authentication details. We can just proceed with an “anonymous” connection. Many data sources will be configured to only allow connections from users with the right access privileges. However, this is outside the scope of this course. If you’re connecting to a file on your computer, you won’t need to enter any authentication details either.
Power Query has successfully connected to the website, and we can see that it has recognized some tables that we can choose to import. We can even see the “Web view” for how the website looks on a browser.
We’ll select the “Best States to Retire” table and load it in. To the left in our Power Query window we can see the query has been loaded in successfully, and on the right, we can see that some applied steps have been automatically generated and applied for us namely, promoting headers, and auto-detecting the column types:
You can click on any of these steps to preview the data as it was right after that transformation was applied. This can be a really useful feature for understanding the effect of your applied transformations.
Since this table is well-formatted, we don’t expect to see any errors in the column names, but sometimes when loading data, especially from csv and excel files, you will get the column headers in the first row of your dataset. Like this:
To make the first row into your column headers you can use the “promote first row” transformation, you can also do the opposite by using the “demote column headers” transformation.
This isn’t very wise to do, but let’s say I want to get rid of the first row which has our old column names. I’ll click on “Remove top rows” and enter 1 into the dialog box. Now that we got rid of the first row, we need to rename our columns so that they are more descriptive. Renaming a column is as easy as double-clicking the column name.
Of course, this isn’t very efficient, so if I wanted to go back on the transformations I applied. I can click on the X next to each step, or I can go to the demoted headers step, right-click, then press on “Delete until end”. Now we’re back to where we started!
If we want, we can reposition columns simply by dragging and dropping them in our desired order. We can also sort the data using a column by clicking on the arrow in the top right of the column name, let’s sort the states by the Crime Rank to find out which state is safest.
After sorting, we can see that New Hampshire is the safest state.
It’s now up to you to apply these transformations to prepare the Adventure Works dataset for analysis.
2. Let's practice!