Using Html.Table() To Extract URLs From A Web Page In Power BI/Power Query M

August 30, 2018 By Chris Webb in M, Power BI, Power Query 21 Comments

Last year I blogged about how to use the Text.BetweenDelimiters() function to extract all the links from the href attributes in the source of a web page. The code was reasonably simple but there’s now an even easier way to solve the same problem using the new Html.Table() function. This function doesn’t seem to be documented online yet, but the built-in documentation for the function available in the Query Editor is up-to-date:

Miguel Escobar also has a great post showing how to use it and the new Web.BrowserContents function here.

Here’s an example M query that extracts all the links that start with the letters “http” from my company homepage:

let
    Source = 
	 Web.BrowserContents("https://www.crossjoin.co.uk/"),
    Links = 
	 Html.Table(
	  Source, 
	  {{
	   "Link", 
	   "a[href^=""http""]", 
	   each [Attributes][href]}})
in
    Links

To explain what’s going on here:

Web.BrowserContents returns the text of the html DOM for the web page
In the second step Html.Table takes that text and searches for all <a> elements whose href attribute starts with the letters “http”. I found this CSS selector here.

21 responses

Claus Kirkegaard says:

August 30, 2018 at 2:12 pm

Great.
But it could be cool if we could use all these great data extraction tools from SSIS also 🙂

Reply
Charley Kyd says:

August 30, 2018 at 2:54 pm

Chris,

This apparently doesn’t work the current version of Excel 365. Data, Get & Transform Data doesn’t recognize either of these functions:
…Web.BrowserContents
…Html.Table

Charley

Reply
- Chris Webb says:
  
  August 30, 2018 at 8:12 pm
  
  Those functions obviously haven’t made their way to Excel yet; I guess they’ll be added in the next few months.
  
  Reply
  - Franco says:
    
    November 2, 2019 at 4:34 pm
    
    After almost two years, Power Query in Excel 365 still does not recognize these two functions.
畅心 says:

August 31, 2018 at 11:17 am

Python.Execute is intersting

Reply
Pingback: Dew Drop - August 31, 2018 (#2794) - Morning Dew
Pingback: Web Scraping with Html.Table in Power Query – BI Polar
Diogo Gonnelli says:

November 21, 2018 at 5:14 pm

Hi Chris. Great Tutorial!
I have a issue with the refresh when I use Power BI online with the Web.BrowserContents. I see this error message:
Query contains unknown funtion name: Web.BrowserContents.
Query contains unknown funtion name: Html.Table.
Do you have any idea to fix it?
Thanks!

Reply
- Chris Webb says:
  
  November 21, 2018 at 5:25 pm
  
  Are you trying to use this in Excel or an old version of Power BI Desktop? Not all versions of the Power Query engine include this function.
  
  Reply
  - Diogo Gonnelli says:
    
    November 21, 2018 at 5:32 pm
    
    I’m using the lastest version of Power BI desktop. With the desktop i can refresh te webcontent, however when I publish to Power BI online I can’t refresh. The error message show that the query contains a unknown funcion related to HTML and Web Content. I’ll keep trying. Thanks!
Hoss SATOUR says:

December 11, 2018 at 11:16 pm

Great, thank you very much Chris 👍

Reply
Pingback: Power BI/Power Query – Web Scraping (a full page with images) – Excel and BI
Pingback: How to get the most out of Web.BrowserContents() - Foster BI
Mark Thim says:

March 4, 2019 at 9:15 pm

Very interesting tutorial Chris, I tried to apply this but I’m still struggling to get data from Yahoo Finance – Income Statement Table. I need the Income Statement (Quarterly data) for Apple from this link: https://finance.yahoo.com/quote/AAPL/financials?p=AAPL&.tsrc=fin-srch

Since the page address does not change, I have not managed to extract Quarterly data instead of Annual data which appears in the initial page.

Do you you have a suggestion on how to manipulate the table selector (Annual /Quarterly)?

Regards,
Mark

Reply
- Chris Webb says:
  
  March 8, 2019 at 3:53 pm
  
  No, sorry, I’m not good enough with CSS selectors to help here
  
  Reply
  - Mark Thim says:
    
    March 10, 2019 at 11:04 am
    
    Ok Chris, the selector I’ve already identified – button.P\\(0px\\)
    the issue is how to manipulate to load Quarterly data
- Mark Thim says:
  
  March 10, 2019 at 11:05 am
  
  ‘button.P\\(0px\\)’
  
  Reply
Pingback: Removing HTML Tags From Text In Power Query/Power BI « Chris Webb's BI Blog
albertan says:

August 13, 2019 at 7:10 pm

Hi Chris, if I’m trying to get a connection to a report that is intranet based (i.e. internal organizational http website) I’m having issues to connect. Do you have any suggestion on how to get that type of connection?

Reply
Pingback: #powerbi Report to browse and watch sessions from #mbas 2019 using the #powerapp visual – Erik Svensen – Blog about Power BI, Power Apps, Power Query
Youcef says:

November 11, 2019 at 3:33 pm

Hi Chris
Is there any function in Power Query that can do the task .

.

i have used this
————————————————————————–
(URL) as table =>
let
Source = Web.Page(Web.Contents(URL)),
Data0 = Source{0}[Data],
#”Changed Type” = Table.TransformColumnTypes(Data0,{{“Column1”, Int64.Type}, {“Column2”, type text}, {“Column3″, type text}}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each ([Column1] null)),
#”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{“Column2″})
in
#”Removed Columns”
—————————————————————————-

let
Source = Excel.CurrentWorkbook(){[Name=”LINKS”]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“URL”, type text}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “Fetchdata”, each Companies([URL])),
#”Expanded Fetchdata” = Table.ExpandTableColumn(#”Added Custom”, “Fetchdata”, {“Column1”, “Column3”}, {“Column1”, “Column3″})
in
#”Expanded Fetchdata”

———————————————————————————

to import data from the web page but it imports companies names only not link address
want to insert a new column and get the link address to get the profile of each company .

Thank You

Reply