Creating a query from a website

Data is not only located in databases, but also in files, online services, and third-party applications as a growing number of users require the ability to connect to information available on the web. The idea behind the web connector is to allow easy and intuitive information extraction from websites. In this section, we will explore the possibilities of this connector and we will connect to a web page to extract data in an easily readable format.

Getting ready

For this recipe, you need Power BI Desktop and access to the following website: https://www.packtpub.com/eu/all-products.

How to do it...

In this recipe, the idea is to retrieve data from the Packt online catalog. By clicking on the preceding link, you will see the following site:

Figure 2.42 – Packt online book catalog

Imagine you want to extract data regarding the books available on this site.

Open Power BI Desktop and follow these steps:

1. Go to Get data and click on Web. Insert the link in the URL field:
   

   Figure 2.43 – Web connector

2. Authenticate as Anonymous (since it is a public website) and click on Connect:
   

   Figure 2.44 – Web connector authentication

3. After authenticating, the following preview window will pop up where, on the left, you can find a list of suggested tables and, on the right, you can see a data preview:
   

   Figure 2.45 – Web tables preview

4. If you click on Transform Data, you will open the Power Query interface and then you can rename and clean up your data:

Figure 2.46 – Web data preview in Power Query

We will try another feature to extract data from the website and test an advanced link by inserting filters at the URL level:

1. Go to Get data and select the Web connector. Click on Advanced and split the URL https://www.packtpub.com/eu/all-products?released=Available&tool=Azure&vendor=Microsoft into three parts as in the next screenshot and click on OK:
   

   Figure 2.47 – Web connector advanced

2. The preview window will pop up. Click on Add Table Using Examples:
   

   Figure 2.48 – Add Table Using Examples button

3. Start naming the columns as follows:

   a) Title

   b) Author

   c) Nr. Pages

   d) Publication Date

   The columns should look like the ones in the following screenshot:

   

   Figure 2.49 – Add Table Using Examples

4. Start filling in the first rows of each column and you'll see the other rows populate automatically:
   

   Figure 2.50 – Add Table Using Examples details

5. Click on OK and you will generate a table within the Custom Tables section that you can select and load into Power Query:

Figure 2.51 – Insert custom table from examples

With these simple steps, it is possible to connect and extract information from a website with a no-code approach. Users can focus on the content of data and not on the process of how to connect since Power Query allows them to do it in a few steps.

How it works...

This web connector not only allows users to connect to data from web pages by leveraging pre-defined tables identified by Power Query, but it also gives the ability to provide data examples from a web page and generate a custom table with relevant information for the user.