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 DemoPower 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:
- This function will accept the table as an input parameter and select only two columns from the table “Content” and “Name” of the images.
- Define the inline function “ConvertOneFile” that will be responsible for splitting all the binary content into multiple Lists with FileName information.
- Loop over all the binary data in the table to call above defined function.
- Combine all the lists for a single image.
- 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 DemoLimitations
- As we are storing all the data of images in Power BI dataset so that could increase the data refresh time of data models.
- 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
