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 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. 

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. Itook 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 imagesThe “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. 

image001

Close the “Transform Editor” 

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” 

image003

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. 

image005

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. 

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.  

Leave a Reply

Your email address will not be published.