High Quality, Dynamic Images in Power BI
Dynamic Images in Power BI
Power BI has an awesome feature where you can define column category types. This allows you to define all values in a column as image URLs. From there, you can use publicly hosted image URLs to populate that column to dynamically view images in Power BI. An example of what this would look like is one column for fruit names and the other for image URLs, using the fruit name column as a slicer to dynamically pick which fruit is displayed.
This comes with an obvious drawback. These images must be hosted publicly! This method, although very useful, is rendered inappropriate when dealing with any sensitive information.
Is there a workaround? Yes! To get around this obstacle, we can encode the images into Base64 strings.
What are the benefits of using Base64 strings as image URLs in Power BI?
No publicly hosted image links, best for content security
Images are stored directly in the Power BI report
Images can be used dynamically
With the following method, you can store an image of any size
What is a Base64 string?
Base64 strings are a set of ASCII characters used to encode or transmit binary data in a lossless way. The need for Base64 came from situations like needing to email an image made of binary code but only 7-bit ASCII characters could be messaged rather than 8-bit binary octets. In other words, the image needed to be encoded as text rather than binary for lossless transmission. This is somewhat similar to our use case as we're trying to store our images as text in Power BI.
Are there limitations?
The character limit for a single cell in Power BI is 32,766. In this article, we'll be using some DAX magic as it has a maximum character limit of 2.1 million.
Walkthrough
Part 1 : Create an Image Table out of Base64 Values
Obtain the Data URL
There are many free image to Base64 websites available to use. These are quite simple and consist of uploading an image and receiving its respective Base64 string. Make sure to include the Data URI which is a prefix that defines the data and file type of your string. This encoding can also be ran as a relatively simple Python script, if that's something you're familiar with.
2. Separate the Data URL into Columns
The next step is to separate the string into 32,766 character (or less) chunks and separate the strings by column. This can be done in free online character counters by separating 32,766 character chunks manually or as a simple Python script, if that's something you're familiar with. Your table may have more or less columns than this example depending on your image resolution.
Tip: Some images may have blank columns due to lower image resolution. That’s OK!
3. Import into Power BI
As the last step in our table, let's connect it to Power BI! Click the Excel Workbook button at the top left and select the new image table we just created.
Part 2: Concatenated Column
Concatenate
The first step we need to take now that we’re connected to our table is to create a concatenated column to combine all of our separated Base64 strings together. This can be done with as many columns as the 2.1 million character limit allows. You can create a calculated column by clicking the “New Column” button at the top of the data view. In this example, it will be a simple concatenation in the following format:
Image = [Your Column 1] & [Your Column 2] & [Your Column 3] etc
2. Change Column Data Type
Now that our column is complete and we've bypassed Power BI's 32,766 character limit, we can categorize the column as an image URL. Click on the column, go to the top of the page and select "Image URL" from the category drop down.
3. Select Visual to Display Images
All the work is complete! Now all we need to do is select a visual that can display images and input our new image field. For this example I'll be using the chiclet slicer.
Now you can use high quality, dynamic images in Power BI without compromising on privacy or security.
Let’s Chat
At FreshBI, we’ve lowered the barriers, risk and cost of developing world-class Power BI dashboards so that you can unlock the value in your data.
Contact us through the scheduling app to start a conversation about how our data visualization consultants can design your best Power BI dashboards today.
Our Latest Blogs
About FreshBI
Based in Canada, South Africa and in the United Kingdom, we have helped hundreds of businesses achieve excellence & success through business intelligence.
Power BI runs in our blood, and we are ready to take your business to next level.