Getting simple HTML table from html file is very slow

[gancw1]

I have an 8000+ records report in HTML format (110,000+ lines) on a SharePoint site which takes more than 1 hour to import.  Manually converting the report to CSV is not an option.  Is there any way to speed up reading the HTML table ?

My last resort is to run a scheduled Power Automate task to use Excel online to convert the html table into CSV or XLS format 

[gancw1]

There are 2 other options that do not use non-Power BI solution:
- Using Web.Page which is significantly faster but requires IE. This means a getway is required if the app is published to PBI service

- Write M code to parse the HTML code. Not too difficult and there are some examples on the web.

[foodd]

Web.Page still functions, and refreshes in the service

let
Source = Web.Page(Web.Contents("https://ssbipolar.com/2021/05/31/roches-maxim/")),
Data0 = Source{0}[Data]
in
Data0

[Anonymous · Accepted solution]

Hi @gancw1 ,

You can reduce unnecessary data before importing.

Or you can use the "From Web" option in Power Query to read the HTML table directly from the SharePoint site first. And then clean up the data in Power Query for the next load or refresh.

You can remove unnecessary columns, rename columns, and change data types to optimize the data for your needs.

Use the "Close & Load" option in Power Query to load the data into it. This will create a connection to the SharePoint site, and you can refresh the data whenever you need to.

If the above steps do not work, you can try running a scheduled Power Automate task to use Excel online to convert the HTML table into CSV or XLS format.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[gancw1]

I am going to explore using a scheduled Power Automate task to use Excel online to convert the HTML table into CSV or XLS format.