Jason Thomas and Gerhard Brueckl have both blogged on the subject of storing images as text inside a Power BI dataset:
http://sqljason.com/2018/01/embedding-images-in-power-bi-using-base64.html
https://blog.gbrueckl.at/2018/01/storing-images-powerbi-analysis-services-data-models/
Since they wrote those posts, however, Power BI has added the ability to set the Data Category property on measures as well as columns in tables. This means it is now possible to have the output of a DAX measure displayed as an image in a Power BI report and this in turn opens up a lot of new possibilities – including the ability to work around the maximum size of a text value that can be loaded into Power BI (see my previous blog post for more details) and therefore work with larger images.
Here’s a rather lovely picture of a rose:
The original is about 2.1MB; I have a folder on my PC where different versions of this picture, saved at different percentages of the original size, are stored:
Using the technique that Gerhard wrote about, where the pictures can be stored as text in a single cell in a Power BI dataset and then displayed (in this case I’m using the Image by CloudScope custom visual) some truncation of the image occurs even with the smallest files because of the 32766 character limit on the length of a text value that can be loaded into Power BI. Here’s what you see when you display the version of the picture that is 20% of the original size, a file of only 113KB:
To work around this, what you need to do is to split the text representation of the image up into multiple smaller text values stored across multiple rows, each of which is less than the 32766 character limit, and then reassemble them in a DAX measure after the data has been loaded.
Splitting the text up in M is actually not that hard, but it is hard to do efficiently. Here’s an example of an M query that reads all the data from all of the files in the folder above and returns a table:
let
//Get list of files in folder
Source = Folder.Files("C:\Users\Chris\Documents\PQ Pics"),
//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
In my next post I’ll show you my original version of this query, explain why it was slow, and try to explain how the version above works and why it is much faster.
Here’s what the query above returns:
The Pic column contains the split text values, each of which are less than the 32766 character limit, so when this table is loaded into Power BI no truncation occurs. The index column is necessary because without it we won’t be able to recombine all the split values in the correct order.
The only thing left to do is to create a measure that uses the DAX ConcatenateX() function to concatenate all of the pieces of text back into a single value, like so:
Display Image =
IF(
HASONEVALUE('PQ Pics'[Name]),
"data:image/jpeg;base64, " &
CONCATENATEX(
'PQ Pics',
'PQ Pics'[Pic],
,
'PQ Pics'[Index],
ASC)
)
…set the data category of this measure to be “Image URL”:
…and then display the value of the image in a report:
Unfortunately, as I also mentioned in my previous post, most DAX functions (and that includes ConcatenateX()) have a limit of around 2.1 million characters so the original 2.1MB file still can’t be displayed, alas:
However, I do think this technique will be useful because it allows you to work with much larger pictures than before.
It can also be useful in other situations too. I recently came across a great new custom visual called PDF Viewer that can display PDF files stored in text form in a Power BI report:
The example file for this visual shows how a large PDF file can be split across two columns in a table; the technique I describe here is a more practical solution to this problem.
I tried it, but the meansure return a empty field.
Do you have a video explain that?
So are you saying the measure returns a blank value? Does the Power Query query load data that is not blank into your table?
I have the same problem that ALEHQUIZ mentions. The measure is blank!
* query load data table images its ok…
Could you send me an example of an image that doesn’t work? My email is info@crossjoin.co.uk
Sorry, my mistake – I put the wrong link to the custom visual I was using in the post. It should have been this one: https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381297?tab=Overview The post has now been updated.
This looks great, perhaps I am doing something wrong but when I drop your code in as a querie while changing the folder path it doesn’t generate a table. I could potentially split up the code to make it work but wanted to check if there’s something obvious I am missing. I have it working from Gerhard’s post.
Ignore this!
Advanced editor did the trick was trying to build it as a function before.
Pingback: Storing Large Images in Power BI – Curated SQL
Pingback: Power Query Performance And Expanding Columns « Chris Webb's BI Blog
Thanks Chris! now its ok! (using the custon visual “Image by CloudScope”)
Hi Chris,
Just to check, this method is only for “Import” or it is works in “Direct Query” mode as well?
Thanks
I’ve only tested it with Import mode, but it may work with DirectQuery too
Pingback: Storing Large Images In Power BI Datasets | Pardaan.com
Pingback: The Data Thread. Issue #1. - Haystack
I’ve followed the steps but when I try to drag the ‘Display Image’ field to the image field in the Chiclet Slicer visual, it doesn’t take it. Does this not work with Chiclet Slicer?
I have images loaded but, despite them being around 50kb or less, they are truncated so that the bottom section is missing from some of the images.
Apologies, my second sentence above meant that using a different method I can get images loaded but they do not display properly. I can’t get images to display at all with the method described in the article above.
I’ve just tried the Chiclet slicer and it doesn’t seem to work with this method.
Hi Chris,
This topic of query optimization should be much more explored. Just a comparison with DAX show how much material there is for this language, from books to blog posts, while for M there is only a few sparsed sources, as your blog and some others for example.
At least from my experience, M is as important as DAX, but many times I found it too slow to process even a small amount of data, reading a file multiple times for example. M should be the as fast and efficient as possible, but I don’t if this a implementation or end user fault.
After this digression, what I would like to know is if the operation on lists, as in this post, is generally faster than operations on tables, and if this approach solves the multiple file readings problem.
Thanks!
I try it and work perfectly. But just one more question … does this work with the BI service on the cloud when it is refreshed
hey Chris, is there a way to use binary image from a SQLdatabase instead of the actual image?