Importing and Analyzing Data From a Web Page Using PowerQuery

Introduction

This article shows how to customize the visualizations in a report to present the data in the way we want that makes the data very easy to understand.

Step 1

Connect to a web data source.

We will first import a web page from a Wikipedia page as given below.

UEFA European Championship

We will work with the Results Table as shown on the Wikipedia page that is as shown below.

wikipedia page

Step 2

Open Excel 2013 and create a blank workbook.

Click on the Power Query ribbon tab and select From Web option that will import the data from a web page. A new window will appear as shown below:

enter url

Now paste the Wikipedia URL into the URL text box and click on OK.

After successful establishment of the page connection, we will see a list of tables available on the Wikipedia page in the navigator dialog windows at the right hand side. We can also preview the table data by clicking on any of the tables.

navigator

Now select the Results [edit] table and click on the Edit option that will open a new window that allows us to reshape the table before creating a report of that table. We need to reshape our table before creating a report because our data is not in a proper shape for further analysis.

Step 3

First remove all the columns on which we will not work except the Year column and Final Winners column. We can remove all the other columns by selecting the Year and Final Winners columns using (CTRL + Click) and right-click on any of the selected columns and click on remove other columns.

other columns removed

Step 4

Our next step is to remove the Details Suffix from the Year column that is not visible in the table preview. However, if you click on any of the numeric values in the Year column we will see the Details value suffix with the year.

details suffix

Procedure to remove Details Suffix

Select the Year column.
From the Transform ribbon tab, click on the Replace Values button.
A new window Replace Values dialog box will open, now type "Details" in the Value to Find text box and leave the Replace With text box empty.
Click OK.

Step 5

Our next step is to remove the rows from the Year column having the year keywords in their rows.

Steps to filter values

Click the filter drop arrow on the Year column.
A new window will pop up showing all the years and uncheck the year keyword option.
Click OK.

We have now cleaned up the unnecessary data from the Year column.

Step 6

Select the Final Winner column and right-click on the column and click on rename and rename this column to Country.

column renamed

Again follow Step 5 to filter the null values from the Country column.

remove null values

Step 7

Now name this query as Euro Cup Winners.

Since we have now performed several steps, query steps are created and are listed in the Query Settings pane under the Applied Steps.

query rename

Step 8

Our next step to load the query. Click on the Home ribbon tab and then click on the Close and Load buttons and select the Close and Load option.

load query

The converted table data will now be loaded into the Excel sheet as shown below.

converted table data

Step 9

Converting data to Chart

Select the table and go to the Insert ribbon tab and click on Recommended Charts. It will open a new Insert Chart window that shows us to insert the recommended chart and click on OK. Here the recommended chart is Clustered column chart.

clustered column chart

Step 10

Creating MAP Power View

Again select the table and go to the Insert ribbon tab and click on Power View, a new sheet will be created and will be opened with a Power View area and Power View Fields as shown below.

PowerView Fields

Click on any value of the table and then click on the preceding Map button (under the design ribbon tab) that will now create a diagram of a map as shown below.

Before plotting our map, ensure the power view fields setting is as shown below.

PowerView fields setting

Here are the countries are plotted on the map with their cup winning year.

PowerView map

Now you will notice, a single country is plotted with more than one color. Let us see what it means.

Let us see, Spain is plotted with three colors which means Spain has won the cup three times in the years 1964, 2008 and 2012.

spain map

Step 11

Creating TILES Power View

Follow the starting of Step 10 and under the Design ribbon tab click on the Tiles button. Now we will be able to see that we are able to select any country from the slider at the bottom that tells us about the cups won by that country and in which year.

tiles

Summary

It is very easy to customize various types of visualizations in our report, in order to present the data the way we want that makes our data easy to read and understand. Using Power BI we can get data from a wide range of data sources and can represent our data in many ways.