Complete Guide To Embed Images In Power BI Reports (Part III)

Introduction

Our previous blog discussed how we could embed images in the Power BI report using binary data, However, that approach wasn’t practical because of the 32,677-character limit of the Text column, which restricts us from embedding the full image. However, in this blog post, I will share an intuitive way to embed images with complete original size.  

Large Binary Data In Dataset

We know that we can’t store Base64 characters (Text representation of the image) more than 32,677 characters, so as a workaround, we have to store this information in multiple rows and chunks. Each of the cells will have less than 32,677 characters, and at last, we will reassemble these cells into a single DAX measure. The idea is to remove the text limitation using the DAX measure. We can store 2.1 million characters in DAX measure, so it’s a lot that could be used to store image information.

Leverage Data Visualization with AlphaBOLD

AlphaBOLD's Power BI services are designed to help you create compelling, image-rich Power BI reports that drive decision-making. Discover how our expertise can elevate your reporting.

Request a Demo

Power Query Function:

Now let’s look into the actual brain of this hack. We need to create a Power Query function using the Transform Editor view.

Execution Flow:

  1. This function will accept the table as an input parameter and select only two columns from the table “Content” and “Name” of the images. 
  2. Define the inline function “ConvertOneFile” that will be responsible for splitting all the binary content into multiple Lists with FileName information. 
  3. Loop over all the binary data in the table to call above defined function. 
  4. Combine all the lists for a single image. 
  5. Convert lists to the table and add an index column. 

(Source as table )=>let 

    //Get list of files in folder 

    //Remove unnecessary columns 

    RemoveOtherColumns = Table.SelectColumns(Source,{“Content”, “Name”}), 

    //Creates Splitter function 

    SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000), 

    //Converts table of files to list 

    ListInput = Table.ToRows(RemoveOtherColumns), 

    //Function to convert binary of photo to multiple 

    //text values 

    ConvertOneFile = (InputRow as list) => 

        let 

            BinaryIn = InputRow{0}, 

            FileName = InputRow{1}, 

            BinaryText = Binary.ToText(BinaryIn, BinaryEncoding.Base64), 

            SplitUpText = SplitTextFunction(BinaryText), 

            AddFileName = List.Transform(SplitUpText, each {FileName,_}) 

        in 

            AddFileName, 

    //Loops over all photos and calls the above function 

    ConvertAllFiles = List.Transform(ListInput, each ConvertOneFile(_)), 

    //Combines lists together 

    CombineLists = List.Combine(ConvertAllFiles), 

    //Converts results to table 

    ToTable = #table(type table[Name=text,Pic=text],CombineLists), 

    //Adds index column to output table 

    AddIndexColumn = Table.AddIndexColumn(ToTable, “Index”, 0, 1) 

in 

    AddIndexColumn

When you execute this function, you will get the following results: you can see Base64 String characters for the image have been distributed in different rows based on the image’s size. Each value under the “Pic” column is less than 30000 characters, so when the data is loaded in Power BI it will not truncate the information. It took 5 rows to store data for photo 2.JPG and only 2 rows to store IMG_0239.JPG because of the different sizes of the images. The “Index” column is important to reassemble the content in the correct order. Without it would not be possible to combine image content in the original position.

Close the “Transform Editor” 

Further Reading: Handling Big Data: Performance And Limits Of Power BI

DAX Measure:

Now the only thing left to do is to create a DAX measure to reassemble all the BASE 64 content into a single measure.

Create DAX measure in any of the table using below given formula. 

Display Image 1 =  

IF( 

HASONEVALUE(‘Invoked Function'[Name]), 

“data:image/jpeg;base64, ” &  

CONCATENATEX( 

‘Invoked Function’,  

‘Invoked Function'[Pic], 

‘Invoked Function'[Index], 

ASC) 

)

This DAX expression will combine the base 64 characters using CONCATENATEX function. First parameter is a table to iterate over. The second parameter is the column to be concatenated. The third parameter is blank, and the fourth parameter is the order of concatenation. This will be responsible for keeping the order of all the concatenation for a single image.  

Set the data category of this measure to “Image URL” 

Now we are all set to display these images in the report. Download “Image Pro by cloud scope” from Power BI visuals market to see full size images in report and use the measure as image source.

Get Visibility into all your Busniess Operations with Power BI

Enhance your business intelligence efforts with AlphaBOLD's Power BI and Power Platform services.

Request a Demo

Limitations

  1. As we are storing all the data of images in Power BI dataset so that could increase the data refresh time of data models. 
  2. We already discussed how DAX measure could store max 2.1 million characters; if we load very large images, it will give an error. 

Further Reading: How To Reduce Dataset Size In Microsoft Power BI?

Summary

This is the last blog of this series, and we are finally able to embed large images in the Power BI report with almost no limitation. It requires a Power Query and DAX expertise to implement this solution.

Explore Recent Blog Posts

Infographics show the 2021 MSUS Partner Award winner

Related Posts

Receive Updates on Youtube