powerbi-training-en

07 Power Query via de GUI

In module 5 we already briefly looked at Power Query: how the window is structured, and how code is built behind the scenes.

However, in most Power BI models you will not need to use this code: most of the work is easily done in the GUI. In this module we load a “difficult” file via Power Query.

Preparation

Start Power BI Desktop with a new, blank report.

New delivery

We were asked by one of our analysts whether data about population figures can be added to the Power BI model. She has supplied the data in an Excel file, which looks more or less like this (for the exact format: open the Excel file):

Total population is based on (…)
Source	http://data.worldbank.org/indicator/SP.POP.TOTL
Terms of use	http://data.worldbank.org/summary-terms-of-use
Country Name	1999	2000	(…)
Canada	30499200	30769700	(…)
Germany	82100243	82211508	(…)
(…)	(…)	(…)	(…)

This is a structure that is traditionally difficult to read into data processing tools:

• Various descriptive rows
• Head row does not start at the top, but only on row 5
• Years broken down into columns (instead of rows)

In an analysis tool like Power BI you would prefer to have it in the following structure:

Country Name	Year	Population
Canada	1999	30499200
Canada	2000	30769700
Germany	1999	82100243
Germany	2000	82211508

However, this data is fairly easy to load in Power Query. Below is indicated which things you can use for this (obviously not in the correct order or with concrete steps). Look where you get!

• Unpivot columns (or Unpivot other columns)
• Renaming Columns
• Use First Row as Headers
• Removing existing steps (in the applied steps under the query settings are some suggested steps by default - there is a good chance that they are not immediately correct)
• Remove Rows

The outcome should look like this:

Bonus: crazy data structures

So far we have loaded data from four types of sources:

• CSV
• SQL Server
• Azure SQL Database
• Excel files in an awkward format

For the bonus, we’ll add one more source to this: the IMDB top 250 movies http://www.imdb.com/chart/top. We cannot directly link analysis about movies to our current model, but it is a great example about Power BI’s possibilities to handle almost any data source.

Below is a screenshot of how the data should look like in Power Query. You can load the data with “Get Data” -> “From Web”.

Tip: You will regularly need the “split column” functionality here

Solution

Here’s the endpoint of this lab: 07-Solution

Video

Here is the Walkthrough video

Next modules

The next module is Module 8: Publishing and Collaboration in Workspaces. Below is a complete overview of all available modules:

1. Introduction Power BI Desktop
2. Reporting on a Dataset
3. Visuals and interaction
4. Drillthrough
5. Self-service reporting
   • Loading CSV files
   • Loading data from SQL Databases
6. Data Modeling 101
   • Relations
   • Cleaning up your Data Model
7. Introduction to Power Query (GUI) (current module)
8. Publishing and Collaboration in Workspaces
9. Calculated Columns in DAX