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.


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


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.


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.


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
Latest posts by Rajendra Gupta (see all)

About Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience. I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines. I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020. Personal Blog: https://www.dbblogger.com I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta