How to export embedded images (base64) to PDF/PNG

[zhuvi1]

Hi experts!

I'm struggling with something I thought would be simple and I wonder if anyone else here knows a way to deal with this.

First of all, I want to explain what I want to achieve: an automatic weekly digest to my company's leadership telling who will be the crisis leader on call for the week, with some overall information on what are the current hot topics. This is the layout so far (and please don't mind about the stick figure placeholder). Black boxes are redacted for privacy reasons:

In order to achieve this, I'm pulling data from several sources, including:

- Excel roster sheets

- Ticketing system

- Sharepoint

My idea was to set up a recurring PowerAutomate flow to refresh the semantic model, convert the report into a PNG file, embed it into an e-mail message and then fire it to some specific distribution lists.

As far as I've tested, all textual information are being successfuly retrieved and the e-mail embedding logic is working as intended, except for one little detail: the crisis leader's photo keep getting broken. It should be coming from Sharepoint's profile pic, but I can't get it to work.

I've tried several different approaches with somehow inconsistent results, such as:

1. Base64 embedded images with custom visuals: I decided to try using Base64 as I stumbled upon Chris Webb's article and it seemed very thorough. Also, I saw these in several places such as Dhruvin Shah's tutorial. The issue with this approach is that whenever I try to export the report, I get a message stating that "This visual does not support exporting":
   
   

   Per my research, I found out that PBI doesn't support exporting some custom visuals (as you can see here and here). So I decided to ditch the custom visuals and stick to the basics.
   
   

2. Base64 images with standard visuals: I tried using the same approach but now, instead of using those custom image visuals, I used the Table, similar to what we see in Artis Consulting's tutorial. I also tried with Slicer, Matrix and New Card, but all gave me the same results. Here, things are tricky: While I am able to see the image on both PBI Desktop and PBI Web, whenever I try to export the report into a PDF through PBI Web or when I trigger the PowerAutomate flow, I get the same results: Both the PDF and the E-mail will present a broken image icon:
   
   
   
3. Sharepoint API: I decided to try something different. I found several articles (such as this one or this one) explaining that you can get a profile picture from sharepoint by referring to <sharepoint root>/_layouts/15/userphoto.aspx?size=L&accountname=<e-mail>. When I tried that, I got an interesting result: PBI Desktop would show a broken icon, PBI Web would actually show the profile picture, but exporting to PDF or running the workflow will show the same broken icon. I started investigating and I thought it might be due to some sort of Sharepoint permission issue or due to the fact that the forementioned URL is actually an aspx file which apparently sets up few session variables and then gives you a temporary image URL, so I thought this might be the issue. Time to try something different.
   
   
4. Microsoft Graph: It looked promising. I got to the dev environment and simply navigated to https://graph.microsoft.com/v1.0/users/<e-mail>/photo/$value, and it returned the picture I wanted. Apparently, in the past, it was possible to connect to Graph using a specific Graph API or OData feed, but this has changed, and connecting Graph to PBI now is another level of pain. I would have to create an Azure App in order to do that, and I don't have permission to do so within my Azure cloud. So, another dead end.
   
   
5. OneDrive Share links: Hoping for the best, I found yet another tutorial, quite recent by the way, by "How to Power BI". He explored several interesting points about how different levels of permission affects the final result. Thing is, his tutorial relied on generating links with public access (which I'm not capable to do within my company) and he was using static images, while I'm doing it dynamically. The issue with that is that when I try to generate a link with the "people with existing access" option, I do get a full path like https://<sharepoint_root>/Shared%20Documents/General/.../FileName.jpeg?csf=1&web=1&e=qrIj4N and I would be able to work with that URL since I'd just dynamically change the filename. However, when I try to generate a link with the least restrictive permission I have (which is "People in my organization), the URL has a different pattern, such as: https://<sharepoint_root>/:i:/s/<group_name>/eBZT4w9fDd1AlodkpVEaHrYBgSw0zyj_vTjLhR1NJfRa5A?e=EwKmkc and each file would have a different token, so I can't control it dynamically.

In my research, I've read that PBI might struggle to export embedded images, I can't have these files hosted in any sort of public URL, and I can't get PBI and Sharepoint to understand each other. I already double checked Semantic Model's credentials and permissions and they seem to be fine - and I believe if they weren't fine, I wouldn't even be able to visualize the photos on the Web version.

I really can't imagine any other approach or what I might be missing here. Any clue or suggestion on how to deal with that?

Thanks!

[zhuvi1]

Any other ideas? Is this a dead-end?

[lbendlin]

I can't have these files hosted in any sort of public URL, and I can't get PBI and Sharepoint to understand each other.

You are not missing anything, and did a very thorough investigation - This is your catch-22.  It would only work if you would make the folder with the images in your sharepoint public, which you really, really shouldn't do.

How many images are there?  Can you embed them all as image visuals , stack them on top of each other, and then play with the transparency?

[zhuvi1]

So, I've tried to manually load every picture as an "image" layer, so I could try to do what you suggested - stacking them on top of each other and playing with transparency. This wouldn't solve my problem completely, but I could buy some time and get the report working while I try to figure out something else to do.

However, here's the catch: Image layers don't have a "transparency" setting. I couldn't figure out a way to programatically set alpha to 100% or to switch layer order - which means that I would have to manually hide/show the image I want before sending the e-mail.

The only alternative I could think about at this point would be to publish several PBIX reports, one with each associate's static picture, and use a PowerAutomate flow to define which report will be sent by e-mail every week. This sounds like a very "dirty" solution, not to mention it's innefficient (as now I would need to have about a dozen different reports to be maintained / competing for CPU resources when refreshing data).

Would this be the only way to go?

[lbendlin]

What's the point of the image again? To remind each person how they once looked like?

[zhuvi1]

Hahaha something like this.

So this is a communication e-mail layout we've been using for a while now, but we kind of manually write it every week - which means we gather information which is available across multiple systems (including the picture) and send it as a newsletter to a broader audience. This way people can easily see who is the person on charge for the week, so even if you don't know me by name, you can see my picture and figure out who am I just by looking around the office.

Anyway, my job is to automate this task, so I thought PBI would be the best way to do it since it can fetch data from multiple sources and it allows me to format the contents the way I want. I'm actually surprised that I can't find any way to dynamically handle images though, maybe I should be using a different tool?

[lbendlin]

I would go with the Base64 embedding and a HTML viewer visual that can render them.

[zhuvi1]

Aww, that's what I was afraid of. And even if I wanted to create a public sharepoint folder, I don't even have access to do so - not to mention I'm dealing with people's personal information, which is protected by law.

Regarding your question, the number of images is not that big - we're talking about 5 or 6. However, it would be very difficult to have all individual images embedded as visuals because the turn over on this team is quite high, so we would need to constantly change the PBIX file by adding/removing visuals. The idea behind this initiative was to automate 100% of this activity.

Wondering if there is any other way to handle this... Why doesn't the embedded base64 data work? Any idea?