Hi all,
Like many others (here, here and here), I have been struggling with the inability of Power BI service to perform a scheduled refresh on a public web source without using a personal gateway.
According to the MS documentation, this is still not possible. (Don't ask me why)
The problem with this is that the M function Web.Page(), which is needed to work with HTML input, requires a gateway to work.
However, I have been playing around a bit lately, and believe that I have found a workaround that is relatively flexible. I'd like to share it here and get some feedback. Maybe some of you have some suggestions on how to improve the code to make it more broadly applicable.
In this example, I am trying to load the table from the following page into Power Query: https://www.w3schools.com/html/html_tables.asp.
The code
let
URL = "https://www.w3schools.com/html/html_tables.asp",
Start = "<table id=""customers"">",
End = "</table>",
Source = Table.FromColumns({Lines.FromBinary(Web.Contents(URL))}),
ToList = Table.Column(Source, "Column1"),
RowCount = Table.RowCount(Source),
TableStart = List.PositionOf(ToList, Start),
TableEnd = List.PositionOf(ToList, End),
RemoveTopRows = Table.Skip(Source,TableStart),
RemoveBottomRows = Table.RemoveLastN(RemoveTopRows,RowCount-TableEnd-1),
List = Table.Column(RemoveBottomRows, "Column1"),
ChangeStart = List.ReplaceValue(List, Start, "<data>", Replacer.ReplaceText),
ChangeEnd = List.ReplaceValue(ChangeStart, End, "</data>", Replacer.ReplaceText),
XML = Xml.Tables(Lines.ToText(ChangeEnd)),
Table = XML{0}[Table],
#"Added Custom1" = Table.AddColumn(Table, "data", each if [th] <> null then [th] else [td]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"th", "td"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Table.Transpose([data])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"data"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company", type text}, {"Contact", type text}, {"Country", type text}})
in
#"Changed Type"
How it works
The key here is to circumvent the use of the Web.Page() function, so here are the steps:
- Specify the URL to crawl.
- The second step is to look at the HTML source code of the webpage you want to scrape, and find the lines in the code that contain the start tag and the end tag of the table you want to import. (the lines containing <table> and </table>). NB: It's important to put the entire line here, so if there are more tags than just the table tags, include them as well.
- Then the data from the page is loaded. The combination of Lines.FromBinary() and Web.Contents() gives you a list of the raw HTML code from the web page.
- Convert the list into a table to locate the position of the table start and table end, and remove the lines above and below the table.
- I changed the start tag and end tag to a cleaner <data> and </data> because there are often other attributes (such as CSS class), that have an effect on subsequent steps.
- Since HTML has a similar structure as XML, we can now use the Xml.Tables() function to transform the raw data to a table. NB: This is where it can go wrong. HTML pages may contain code that does not comply with the more strict XML guidelines. If you get an error, it will tell you on what line it goes wrong. If that happens, go back to the list and filter out the lines that give the error.
- Once you have converted the raw data back to a table, you can go ahead and transform the data back into the form that you wish. In this case, I have transformed it back to resemble the original table from the webpage.
I have tested this strategy with Power BI service, incremental refresh and scheduled refresh and it works like a charm.
If you have any suggestions on how to improve this code, please let me know.
Cheers,
Kirvis
