Hi readers, Happy New Year! It’s been a while since I blogged and a lot of changes have happened in the last one year, including me moving to Microsoft as a BI Technology Solution Professional in the Healthcare Vertical. As part of my new role, I get to spend a lot of time with customers educating them on Power BI and how websites like https://websitehosting.com can make a great difference to budding e-commerce websites. During these engagements, I also tend to get a lot of technical questions and one of the questions that came up recently was – How can I embed images for my data categories into Power BI without providing the image URLs?
As usual, my first question back to the customer was – why do you need this feature or what is the use case here? Some of the reasons he gave were:-
1) The image will be stored internally in the pbix file, so there would be no need to host the image somewhere else. Currently, they are downloading the images and hosting it on their SharePoint Online site.
2) Some of the public reports are using images that are hosted in public sites (like Wikipedia), and there is a chance that the image URLs might change (and hence stop displaying the image in the PBI report)
3) You can access the images offline (for eg, if you are working on Power BI Desktop and there is no internet connectivity).
And I completely understood his concerns as I had the same issue with some of the public facing reports that I made, for eg., the US Election report that I had made 1 year back. The images for the candidates were sourced from Wikipedia and certain candidates like George Bush, Donald Trump, etc. are not displayed, because the image URLs are no longer valid.
This is where you can use my workaround to embed the images within the report by converting the images into Base64. Follow the steps below:-
1) Choose the image that you want and use any Image to Base64 converter to get the Base64 code. I used this website but you can use any.
2) Once you get the Base64 code, prefix it by data:image/jpeg;base64, and not you can use it as an expression in any calculated column. In my case, I want to create a new column that will display the existing URL link if the candidate is not George Bush or Donald Trump, and use this new base64 code for George Bush & Donald Trump.
Img = SWITCH([Nominee],
“George Bush”, “data:image/jpeg;base64, base64code“,
“Donald Trump”, “data:image/jpeg;base64, base64code”
,[ImgUrl]
)
Note that I am not writing the actual base64 code in the formula above, as it is very long. You can substitute the base64 code in the yellow highlighted area.
3) Make sure that the category of the column is set to Image URL. Even if we are embedding the image, the data category has to be set as Image URL. This also enables us to mix and match as in my scenario, where some of the entries are coming in as actual URLs and some as base 64 code.
4) Now you can use this column in your report for displaying images. In my case, I am using the chiclet slicer to display the images. In the gif below, you can see how the original column (ImgUrl) is not working for George Bush and Donald Trump, but when I replace it with the new calculated column (Img) with the base64 code, it shows the appropriate images.
That said, there are some things that I have noticed while working with base64 code:-
1) Large images usually do not get displayed even if you paste the right base64 code. So make sure you are using small images with this method.
2) Since the base64 code can be really long, the code might soon get long & unwieldy if you are using it for multiple images.
3) Again, due to the length, you might not be able to add it to excel files (excel cells have a limit of 32,767 characters and the base64 code could easily be longer than that). Your best bet would be to add it as a calculated column most of the times.
Let me know in the comments if you notice anything else or have any more input on the same.
Update (01/05/2018)
Gerhard Brueckl (blog | twitter) has already taken this a step further and talks about automating the process by doing everything withing Power BI. Don’t forget to read his blog on this too – Storing Images in a PowerBI/Analysis Services Data Models
Great post!
…
Maybe encode url once with R, and M (DAX would have been nice) for try online-url otherwise offline code64.
Jason, that’s a pretty cool trick. Thanks for sharing
Very nice post Jason.
Congratulation on your new position.
Thanks buddy! 🙂
Hello,
Power BI freezes trying this…
Try using a low resolution image. Ideally it should not freeze.
I’ve got a challenge for you.
I’m attempting to do something similar but for a report based on a streaming Pushed Dataset.
As such , I am still able to create a power BI report (rather than a using a dashboard tile) but I’m not importing the data, I don’t have access to edit query or any of the Modelling functionality.
So, can I still display conditional images, if I can’t define a column as an image?
Hello Jason! Very good post and thank you for share with us.
This really is helpful. Thanks for posting.
These images can be converted to base64 encoding within Power Query using Binary. ToText function but there’s a cell limit of 32766 characters though
Thanks! i’ve tried that with small images and it worked but what is really the issue with large images?
I think it is just string length
Great post and this works really well when working with small images.
Now I have a database with larger images inside and i don’t have the possibility to resize the images at the source…
The larger images are being partially displayed but they are being cut-off (this means that only a part of the image is displayed but not the whole image)
the string length could be the limiting factor and i’m curious if there is a way to perhaps extend this so larger images can be displayed?
Thanks in advance!
Hi Jason,
I applied this technique to convert the images from a local directory. I set the table of the images to “not include in data refresh”. However, after publishing it to Power BI Service, I still can’t set up a refresh schedule for it. The error says that it’s an unknown data source. Is there a workaround for it? Thank you for your post.
Regards,
Teresa
Hi Jason,
Will this technique also works for image stored in SQL Server. And also will it work if we’re using Direct Query ?
As you might know Direct Query would not allow us to change the column type unless it is switched to Import. Might want to know how we handle it if it is a Direct Query.
Another thing is, regarding the string length, if it is more than 32766 char, will the image crop or Power BI will show a system icon like a broken image icon ?
Thanks,
Axel
Hi Jason,
does it works for Direct Query method ? The thing is, it is needs to change column type and all, as my understanding it is not allowed and always want to change to Import.
But I do need to use Direct Query though, has not succeed until now.
Thanks,
Thank you for this solution! This was very helpful! Does the string length apply to a table created in Power BI?
Hi,
Is there a way to solve the issue of large images (above 32,766 characters)? The images are stored in db and can’t be resized manually.
Please help.
Thanks a lot