Power BI | Custom functions exercise | Get the external links from a selection of websites

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Software ==> Power BI  (111 exercises)
Version ==> Latest update
Topic ==> Custom functions  (3 exercises)
Level ==> Harder than average
Subject ==> Power BI training

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Start by either creating a new table of website URLs, or loading them in from an Excel workbook (which you will need to create):

Website URLs

Obviously you should use real website addresses!

Your aim is to get a single query showing all of the links to external websites containing in these URLs.  Begin by doing the following:

Task Notes
Create a parameter Create a parameter to hold a typical website URL.
Load web data Load any table of data from a typical website URL.
Apply your parameter Change the query you've just written so that it loads data from the website whose URL is held in your parameter.

If you look at your query you should see that it processes the HTML returned from a website using an M Html.Table function:

Html.Table function

Step 2 of your query will start something like this.

 

Amend this to create a new column called External links which picks out all the <a> tags (ie the hyperlinks) which have href attribute beginning http:

Changed Html.Table formula

You need to type this in very carefully!

 

Turn this query into a custom function, and apply it to your list of website URLs to show the external links for each website you've requested:

External links

Your final query should show all your websites, and for each website all of its external links.

Apply your changes to return to Power BI Dekstop, then save this query as PBI web scraper, and close it down.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 0 threads Add post