Rajendra Gupta
View updated data

How to use a Web data source in Power BI Desktop reports

March 30, 2020 by

This article explores various ways to use a Web data source in Power BI Desktop reports.

Introduction

Information is widespread these days. You might get required data from Excel, CSV, text, PDF file, various relational and non-relational databases, SharePoint, etc. As you know, the internet provides us with an ability to view any data from any part of the World using web pages. We do not want to copy data from a webpage in an excel and then import it in Power BI Desktop.

Power BI gives you a lot of options to import data and prepare reports, visualizations for data interpretation. We can import data directly from a webpage using the Web data source.

To view available data sources, launch Power BI Desktop and go to Get Data.

Get Data in Power BI Destop

  • Note: In this article, I use the March release of Power BI Desktop. You can use the URL to download the latest version. It is always good to use the latest version to use all available features. Download March release

Import tables from a Webpage using a Web data source

Suppose we are creating a report that uses Olympic 2016 medals tally data source. We need to get data from a web page. For this click on Get Data -> Web for it. Specify URL.

Web data source

Click OK and allow anonymous access to the web page.

Specify a URL

Once we click on Connect, it establishes a connection to the URL specified.

Connection status

You get a list of autodetected tables from the web page. Select the appropriate table, get a preview and use it further.

Autodetected tables

You can select the required table and load the data for creating a visualization. We use Transform Data to customize data based on our requirements. It allows us to remove duplicate values, create new columns, define table headers, use calculated columns etc.

We can create the report as it is out of scope for this article. You can refer Power BI articles on SQLShack for more details.

Add table using examples

Suppose you want to explore the product and their prices from an online shopping website such as Amazon. Here, the web page does not contain any tables that we can import.

In the following screenshot, we have books listed on Amazon. We require to get the following field in a tabular format.

  • Book Title
  • Author Name
  • Paperback book price
  • Kindle edition price

Example

As you know, we do not have this data in a tabular format on Amazon so think of a question – Can we import required data from Amazon for visualization?

Yes, we have a solution for it. Let’s explore it.

Click on Get Data-> Web. In the From Web, pop-up box paste into the web URL box that you copied from the web page.

Specify a URL

Click OK and see if it gives any tables to import data. We get a few tables, but it does not contain any useful information that we required in the source data.

Navigator

Look at the option Add table using examples in this data preview page.

Add a table using examples option

Click on the highlighted option, and you get an interactive window with the following information.

  • First part shows the web page
  • The second part shows an empty table. initially, it shows a single column

Specify columns

In the second part, we need to specify a sample of data that we wish to import. For example, we require a book title in this column.

In column 1, type the text, and it gives suggestions for data from the web page. Select the required book title from the drop-down list.

Specify a sample value

Once you select a value for column 1, Power BI automatically fills similar kinds of values (in this case book title) for other rows.

We can easily distinguish user entry and automated populated values. It shows user text in the regular font, but automated populated values are faded.

Auto filled column data

Double-click on column 1 and rename it to give an appropriate name. Here, we give it the name Book Title.

Rename column

In the next column, we require a book author. Click on *, and it adds a new column in the dataset. It uses a few functions to look for data from the web page.

In the following screenshot, check that for row 1, we entered the star rating (4.7 out of 5 stars) while for the second row, we entered the author name. You might need to look at a few combinations to get the proper data. We can modify data later using the power query editor.

Add a new table

In the third column, add the cost of the paperback edition. Some of the books contain Kindle edition as well. The price for a kindle edition is different than the paper book. Let’s add a new column and type the price of a kindle book. Here, we get few blank rows because Web URL does not contain the relevant section (in this case kindle e-book price).

Fill details for columns

Now click OK, and it gives you a preview in the navigator window. You can directly load or transform data as per your requirements.

View table data

Click on Transform Data to edit the data in Power Query editor. Here, we have an incorrect Autor name in the first row.

Replace value

Right-click on the first row and replace the value.

Replace the values

In the replace values, specify the new value to replace the existing value. You can verify the current value as well.

Specify a new value

Similarly, I replaced the author’s name for row 13. We can see updated data, as shown below.

View updated data

You can make further changes data as per your requirement. Once changes are done, click on Close & Apply to save the changes. If you want to discard changes, click on Close.

Click on Close & Apply

New Web table interference preview feature

In the above section, we saw that add tables using examples does not work correctly. We may need to try a specific combination to get table data. It cannot get accurate data after that. You may need to transform data, and it might be a tedious task for a large data set. In this section, we will look at the solution to this issue.

Power BI Desktop releases new features regularly in the monthly release. In these new features, few features come as a preview feature. We cannot directly start using the preview feature. To enable this feature, go to File-> Options and Settings -> Options. It opens various configuration options. Navigate to Preview features.

In the preview features list, put a check on New web table inference. It enables this preview feature.

Preview features

Click OK, and it asks you to restart Microsoft Power BI Desktop to use the recently enabled preview feature.

Restart message

After a restart, again go to the web data source and specify the URL that we used in the earlier example.

Specify a URL

Click OK, and here you see a few suggested tables. These suggested tables come as part of the preview feature. Here, we see 8 table suggestions from the web page data source. These table suggestions entirely depend on the web page and the Power BI algorithm.

View auto suggested table

Put a check on a table, and it gives you a preview of the suggested tables. Select one that best suits our needs. In the following screenshot, you see table 2 with many useful columns.

View table data

You can still use the Transform Data and do required data changes.

  • Note: New web table inference is still a preview feature. You might notice significant changes in the upcoming Power BI Desktop release. You should be a little cautious with using these features in production reports.

Conclusion

In this article, we explored different ways to import data from a Web data source. I am excited about the New Web table inference feature as well. You should explore this feature for your data requirements.

Rajendra Gupta
PowerBI

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

14,061 Views