SQLServerCentral Article

Import Web pages with Power BI

,

Introduction

In this article, we will learn how to create charts from web pages with Power BI. Power BI is an extremely great tool to easily generate reports. We will combine and analyze data from different websites, and we will talk about COVID 19 with the reports.

In order to analyze the information, we will use the worldmeters.info website to get information about dead people, infected, new cases, etc.

Web page with COVID information

On the other hand, we have a different website, the graphic.reuters.com site contains information about vaccinated people. Can we combine the information from both sites to get nice reports?

Vaccinated people

The answer is yes. Power BI allows to easily combine data from different sources like a web page, SQL Server, Azure, Oracle, REST API, Excel, JSON, XML files, and many other options.

Requirements to import web pages with Power BI

  1. First of all, an internet connection.
  2. Secondly, Power BI Desktop installed.

Getting Started

First of all, open Power BI and select the Get data option.

Get Data in Power BI

Secondly, select Other>Web and press connect.

Web pages with Power BI importation

Thirdly, enter the web URL. In this case, enter: https://www.worldometers.info/coronavirus/.

URL used to import Web pages with Power BI

You will see multiple tables from the website.

tables to import Web pages with Power BI

Also, the web view tab allows to see the current web page to check the data.

Select the tables to import Web pages with Power BI

Select Table1 for this example. You have 2 options in this case:

  1. You can load the data as it is. This data can be then modified.
  2. You can transform the data. We will use this option.

Click Transform Data at the bottom, as shown below.

Web view preview in Power BI

Transform Data to import web pages with Power BI

One thing you can do is change the name of the table. In this example, we will call it CovidCases. We can edit this in the right side panel.

Rename tables

In Power BI, you have the applied steps pane. This is great because it has the list of changes applied and you can remove steps or recover steps removed if necessary.

Applied steps history with Power BI

You could rename columns, but you may have problems extracting the data. Rename the column later.

Modify column names to import web pages with power bi

Also, it is possible to remove columns. Remove any column that you do not consider necessary for your reports. Note that you can replace values, change the data type, unpivot columns.

Remove columns

In addition, remove the extra columns with null values.

Remove columns in Power BI

Furthermore, you can add filters to your data. We could for example filter the Continent. In the image below we filtered per continent and we select South America.

Use filters to import Web pages with Power BI

Finally, save your transformations by pressing Close & Apply.

Close and Apply transformations in Power BI

The Other table

We also have the table from graphics.reuters.com. We will repeat the same steps as we made with the other website.

Get data from a web page.

We will use the URL to get the vaccination information.

URL from the second web page

We will need to rename columns. Column 1 will be countries, column2 vaccinated percentage, column 3 infections, and column 4 deaths.

Data of the table to import in Power BI

After all the changes, your table will look something like this.

Table of Vaccinated people

Finally, apply the changes.

Apply changes in Power BI

Create a Relationship

There is an option at the left to see the relationships between tables.

Create relationship between 2 tables in Power BI

Working on the Power BI report

First of all, click on the report icon on the left to see the report generated

Reports

In this section, we will show the parts of the report. You have the report canvas to drag and drop the report components. You have a menu with different options. Also, you can apply filters with the filter pane and you also have the Visualization pane to see all the available reports.

In addition, you have the Fields pane with the 2 tables of the 2 sites imported. You can drag and drop columns from there.

Panes in Power BI

You could for example filter by continent.

Apply filters in Power BI

In this example, we will use the table component to visualize data.

Drag and drop a table

In addition, you can drag and drop the vaccinated people from the other table in the report. Note that you are now having a report from different tables.

Drag and drop columns on the table in Power BI

Furthermore, we can use the maps to have a graph of COVID cases per country.

Maps in Power BI

As you can see, the data visualization is impressive.

The Power BI map with covid cases.

Conclusion

To conclude, we can say that Power BI is an extremely powerful tool to easily generate reports. In this article, we show how we could extract and transform data from 2 different web pages, create a relationship between both. Also, we show how to create filters, drag and drop data and create a report with maps.

Rate

5 (3)

Share

Share

Rate

5 (3)