Get data from htm file to Power BI

[Anonymous]

Dear all,

Please your advice for below issue.

I am getting data from a htm file saved in SharePoint to Power BI.

Issues: There are separated tables in this file, while I want to import all tables from this file to Power BI insteads of select each table and separate in multiple query.

Expectation: Could you please advise if there is any way that I can import all table 2,3,4,5,6,7,8, one time to Power Query?

Thanks for your support!

[v-jingzhang]

Hi @Anonymous 

You could refer to the second method in this article: Web Scraping 1: Combine multiple tables from one page in Power BI and Power Query – The BIccountant

Or in your screenshot, you could try right-clicking on HTML Tables [8]. If a menu pops up having "Transform Data", you could click on "Transform Data". Then you will enter PQ and see the Source step which lists all tables there. (The right-click menu doesn't always display when PQ chooses different connector functions. In that case, you can try the method in next paragraph.)

If your Source step doesn't display a table containing all tables but displays HTML codes like below. Replace its original connector function Web.BrowserContents with Web.Page(Web.Contents(url)). This will change output format. 

Once your Source step has all tables in the webpage, you can filter rows to only keep the tables you want. Then expand Data column to have all data in one query.  

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[mahoneypat]

Instead of using the wizard, you can use the Folder connector (or SharePoint Folder), filter to the file of interest, drill into that Binary and then use Web.Page to see all the tables in one query.  You can then filter to the tables you want, and then expand those.  Example M code below.

let
Source = Folder.Files("c:\test"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "TwoTables.html")),
FileContent = #"Filtered Rows"{[#"Folder Path"="c:\test\",Name="TwoTables.html"]}[Content],
Custom1 = Web.Page(FileContent),
#"Filtered Rows1" = Table.SelectRows(Custom1, each ([ClassName] <> null)),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Data", {"Car Name", "Price"}, {"Car Name", "Price"})
in
#"Expanded Data"

Pat