Loading Dynamic HTML sources into Power BI - possi...

Raalu · ‎11-03-2023

Hi,

I have a report in Power BI that I'm building which connects to an excel database, loads a set of data and then uses a parameter from that database to connect dynamically to a set of excel files osted in SharePoint - E.g. I choose the parameter Apple and then the set of excel files related to Apple will load in too.

I have a set of tables and charts in this dynamic excel fileset that I have had trouble recreating in Power BI (see an example below), given all the null spaces and merged columns. I don't know Power Query well so I've had trouble recreating it from a raw source.

Instead, I wrote a VBA script that publishes the charts and tables as HTM in the same location as the specicifc excel file (e.g. same location as the Apple excel) and this produces working dynamic connections.

    With wb.PublishObjects.Add(xlSourceRange, wb.Path & "\Charts&Tables\HistoricalFinancialSummary.htm", "Output", "$B$91:$U$111", xlHtmlStatic, "", "")
        .Publish True
        .AutoRepublish = True
    End With

This seemed to be the most stable way to create images automatically of the relevant tables and charts. I tried a different method creating PNG files but the VBA script quickly encountered memory issues that made the solution unworkable (but could possible be a solution, albeit lower image quality, if others have had success here).

However, I have two issues that I have struggled to solve with the HTM files within Power BI

1. When I make the dynamic connection to the HTM file, typically via Web.BrowserContents, the code that is loaded isn't just HTM (maybe some JSON, its quite substantial so I haven't pasted here) so the image doesn't load in the report when put into a HTM Visual (e.g. HTML Content or HTML VizCreator Flex).
- I have tried via File.Contents too, which loads the file but extracts the table from HTML (HTML.Table). I could try recreating the format here but I thought this was the more difficult option and it didn't seem to work for files hosted online (https:// link).
2. Even if I put the raw HTML code directly into a query and load that in a visual, only some of the saved images and charts load. Again, code and example output below

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 15">
<link rel=File-List href="Chart1_files/filelist.xml">
</head>

<body>
<!--[if !excel]>&nbsp;&nbsp;<![endif]-->
<!--The following information was generated by Microsoft Excel's Publish as Web
Page wizard.-->
<!--If the same item is republished from Excel, all information between the DIV
tags will be replaced.-->
<!----------------------------->
<!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD -->
<!----------------------------->

<div id="Syndication_11775" align=center
x:publishsource="Excel"><img
src="Chart1_files/image001.gif"
alt=Chart1></div>


<!----------------------------->
<!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD-->
<!----------------------------->
</body>

</html>

I'm really at a loss as how to proceed. I feel like this is possible and I am close to a solution but nothing works consistently. Has anyone had success with taking images of excel charts and tables and loading them into PowerBI without having to paste the raw code (and therefore it not being dynamic). Alternative suggestions like recreating the VBA script to save the charts / tables or loading as MHT and recreating the formatting are welcome, but I'm not properly versed to get this to work.

Pascal_KTeam · ‎11-09-2023

Dear @Raalu
This is a very interesting case!

My company is developing the HTML VizCreator visuals and we would like to help you out. I suggest the 2 of us have a quick meeting to talk through the case, you provide a sample file and I have our team figure out a solution. Once we have it, we post it back here for the community to see.
How does that sound?
Please simply reach out via https://bisamurai.com/contact/ and we'll work on this together. Looking forward to solving this 🙂

MFelix · ‎11-09-2023

Hi @Raalu ,

Believe that you are going the long way to solve your issue, if you already have the information into Excel it would be much easier for you to get the information in Power Query (even knowing very little) than to upload your image into a HTML and then make Power BI read your HTML image.

Is the information you present the base file, or is this the summary of the information you want to show? What I mean is if the excel file is already in it's raw format or is there data behind it?

If you want you want can you please share a mockup data. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

I can make an example on the power query how to get the dta and calculations.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Raalu · ‎11-13-2023

Hi MFelix,

Thanks for the response!

Okay, that doesn't surprise me, I had a feeling I wasn't tackling the issue from the right angle. The first image I posted is an "output" of raw data found in the same excel file but from a different tab. I essentially want to recreate that table in the Power BI output.

I also have some excel charts - the HTML i posted is from one of these charts, rather than the table in the first image - so far I haven't found a solution that works for both. Similarly, these charts are output versions of another tab that contains the data needed to make the charts / graphs.

I will send a private message with a mock-up of that table though! I appreciate you taking a look.

Thanks,
Hugo