Recently, a member of our team ran into a client request that posed a bit of a challenge. The client wanted to display employee pictures in Power BI reports using images saved in a database. But the image data was stored using a varbinary (max) data type column. The problem? Power BI can only display images if they are categorized as URLs, because the URL is a pointer to a location where the image exists as a file.
Back in the day when Power BI was a set of tools in Excel, it was possible to load images straight into the data model (PowerPivot) using the binary column data type, and then categorize that data as an image native to the model itself. Times have changed. To get an image into a Power BI report now, you must use a URL, either from an external website or a file system. There are two main drawbacks to this method:
- The images will not load if you are working offline
- If the file is moved to a different location, or if the name is changed, the image will “break”, or be unable to render
How to embed images
The good news is that there are workarounds to this challenge. We are going to reconstruct the above use case and demonstrate how to pull in images from a local database, and then use custom columns in Power Query to reformat the source data in a way we can render graphically.
Note: This same custom column technique can also be used to display images imported using a local folder as a data source. If you have Power BI Desktop installed, you can work through along with this post by downloading the .pbix file with this link.
In our sample database we have a couple of tables containing images stored in a binary format, as well as a few columns of metadata for the images. The images being used here are a JPEG file type, but this technique can also be used for PNG files. We imported the data into Power BI and loaded two tables of images: Examples 1 and 2. For the first example we used three small images in the table, as shown below.
Example 1
First, we will break down the custom column creation into two steps. Later, in the Example 2 table, we will explore how to combine the technique into a single custom column. The first step is to convert the binary column ‘PictureData’ into text. Using the following code with a custom column in the Power Query Editor window will accomplish this:
Binary.ToText([Column to covert], BinaryEncoding.Base64)
Using the Binary.ToText function the binary column has now been converted to a Base64 string. This is important, as a standard non-Base64 conversion will not work. However, we are still missing the prefix that makes it a URL. A second calculated column using the following formula will get us to a usable URL column:
“data:image/jpeg;base64, ” & [BinaryToText]
If you are converting png files, the code you would use is:
“data:image/png;base64, ” & [BinaryToText]
The final step is to set the Data Category of the ‘ImageURL’ column to “Image URL” so that Power BI will recognize it as a URL rather than regular text and display the image.
Now you can use those images in a Power BI report without worrying whether online graphics will result in broken links.
Character limit poses challenge
There is one major limitation to using this technique and it stems from the string length allotment in Power BI. Power BI has a maximum string length of 32,766 characters. This causes issues when trying to embed large images.
To demonstrate this limitation, I took the same image and replicated it multiple times adjusting the size of the image. For testing purposes, I also added pixel width and height measurements as well as the length of the ‘ImageUrl’ string as columns.
Example 2
The dataset used for Example 2 also combines the two formulas – used separately in Example 1 – into a single formula. The combined formula looks like this:
“data:image/jpeg;base64, ” & Binary.ToText([Column to Convert],BinaryEncoding.Base64)
As you can see from the picture below, the image starts to truncate once we get to the 400×440 pixel image. As you move down the list and add larger images, more and more of the image gets truncated even though the image within the report stays the same size. Note the string length for the images, and how after the second image, all of the lengths say 32,766. The string for the image is being cut off at the 32,766-character limit.
As of this writing there is no way around this issue. If you have the option, you can scale down images until they are under the limit and can be fully displayed.
Want to learn more?
There are a few ways to work through these issues. Microsoft’s Jason Thomas also covered Power BI URL workarounds earlier this year using a different technique. His method demonstrates how to embed images by using an external website to convert to Base64 Code, where this post focuses only within Power BI. You can read more about Jason’s method here.
If you need further assistance, we help teams master the platform to quickly build stunning visualizations and share valuable data insights. Contact us today to learn more.
