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?
My first reaction is to say no – this feature was actually removed from the product some time ago, I think because it was a security risk. However there might be a way around it… I would need to test some things first.
Looked into it myself and it is possible it’s the same code just the source and the selected columns are different
That’s what I thought might work. I’ll try to get around to writing it up as a blog post.
I was able to get this to work with binary images from my SQL db:
let
Source = Sql.Database(“server_name”, “database_name”),
dbo_table = Source{[Schema=”dbo”,Item=”table”]}[Data],
RemoveOtherColumns = Table.SelectColumns(dbo_table,{“image_id”, “binary_image”}),
SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000),
ListInput = Table.ToRows(RemoveOtherColumns),
ConvertOneFile = (InputRow as list) =>
let
BinaryIn = InputRow{1},
FileName = InputRow{0},
BinaryText = Binary.ToText(BinaryIn, BinaryEncoding.Base64),
SplitUpText = SplitTextFunction(BinaryText),
AddFileName = List.Transform(SplitUpText, each {FileName,_})
in
AddFileName,
ConvertAllFiles = List.Transform(ListInput, each ConvertOneFile(_)),
CombineLists = List.Combine(ConvertAllFiles),
ToTable = #table(type table[image_id=text,image=text],CombineLists),
AddIndexColumn = Table.AddIndexColumn(ToTable, “Index”, 0, 1)
in
AddIndexColumn
Hey XANDER,
I almost got this working but I’m not familiar with M so I’m at my limit now. Would you be able to help me out for a fee?
I do not think this works with direct query? as i cannot convert binary to text then?
Hi Chris. First of all – thanks a lot for this post.
I have a problem with using yours and Gerhard’s approaches to show images in cards. When I am using default “multi-row card” visual in power bi I see images as images when they are uploaded using public URL. However if I try to use your method and upload images from a local folder I will see a plain text instead of picture. Whereas in a “table” visual I see images as images in both cases. Any ideas what might be wrong with card visual?
Pingback: Ako zobraziť obrázky z databázy v reporte Power BI - Power-BI.sk
Hey Chris, thanks for this post.
I want to make the image base64 results appear on one row.
Would it be possible to convert the PIC columns to rows so that each image has a single row with multiple columns for base64 values and then make the DAX statement concatenate each column instead of each row?
Why would you want to do this? The trouble with this approach would be that you would have an arbitrary number of columns to combine the data from, and that would be impossible to do in DAX; splitting the data across multiple rows is the only way to concatenate the text easily.
Chris, master, you can give an example using connection to an sql database that is where I have my images to give me a more general idea of how to interact.
It will be a great help, a big hug and my usual respects.
It’s a small change from the code here – I’ll put together an example when I get a moment.
Hi Chris, any luck on this sample. I’m trying to do the same with a SQL data source
Hi Chris, Can you guide me here. I have 1000 image folders for 1000 employees stored in drive, each folder having max of 6 images each. I have created a report in Powerbi and need to show images according to respective row of employees.
is there any ideal approach to achieve this.
Pingback: Powering Up MYOB’s New Foreign Currency functionality(Part 2) – Five Ledger
Awesome post Chris,
However, everything seems to check out and the table is populated with indexed strings but after creating the measure, enabling image url and inputting the image in CloudScope visual, nothing appears. Just an empty cell. First time working with M so maybe its a configuration thing but I added a second file and it was automatically sharded and indexed so im not sure what the issue is…
Disregard the above but the image comes in ginormous. Is there a way to resize?
Are you sure that you’re not seeing the actual size of the image?
Pulling the arrow to decrease the height wouldn’t scale the image, but I discovered decreasing the width will scale the image down. Also facepalm because in my initial comment I was using a PNG file. Thank you so much for this tutorial!
Hi Chris,
I’m getting an error on line 12 of your M Query “Token Literal is Expected” and the ampersand sign has a red squiggle under it. Any idea why?
Pingback: Chris Webb's BI Blog: Power Query Performance And Expanding Columns Chris Webb's BI Blog
Pingback: Powering Up MYOB’s New Foreign Currency functionality(Part 2) – Five Ledger
Brilliant Solution! I was able to get images up to 1Mb but I am facing a more advanced problem. How do we deal with multiple image files? I have 4 images in the same folder which would also need to be brought in.
You can view the image you are interested in by simply utilizing a slicer. Each image will have a number of rows that divides the image up. But only the rows that are important (based on slicer selection – make sure only 1 selection can be made) will ‘remain,’ and the desired image will be displayed.
Thanks so much for this Chris; does the code in the measure need to be changed in order to display different images, I’m only get one image to display?
Yet another fantastic post. Thanks. I have a question that is triggered by this post but is only remotely related to it. Hope you don’t mind if I ask it anyway.
I have a custom connector that reads data from an accounting system by wrapping a URL in OData.Feed. The custom connector handles the OAuth2 token exchange and everything works well. OData.Feed accepts “Concurrent = false” as option so I never get asynchronous requests messing up the OAuth2 tokens. Everything fine so far.
Among other data, OData.Feed returns URLs to PDFs of invoices that I would like to process with the technique in your post (as I said: remotely related). The URLs look something like https://accountingsystem.com/Attachment.aspx?ID=e84549d8-f273-4704-8fb4-000b2e535d4a&Division=123456 and do not work if I wrap them in OData.Feed. Instead I am using Web.Contents() and process the result with your technique. It works for fetching a single PDF.
However… There are some 30.000 PDFs, each with its own URL. I have not been able to get Web.Contents() to execute synchronously (there is no “Concurrent = false” option). As a result, things run async and the OAuth2 token dance doesn’t work anymore because there is another call before the refresh-token has been received.
My question: Is there a way to do sychronous calls to Web.Contents() ?
Any help or pointer is highly appreciated.
I don’t know, sorry. Maybe you could use Function.InvokeAfter to add a delay in between the calls?
Figured it out. I check for error code 401 and use the wait-retry pattern that is documented here: https://docs.microsoft.com/en-us/power-query/handlingstatuscodes
I have implemented the solution using above concept it was working well till last week(30/04/2020) i am not sure what happened but suddenly it stopped working now, any idea or suggestion. All my image files are less than 400KB. Strange thing is same issue in my archived Dashboard as well. I am not updated my power BI Desktop and using December 2019 Version.
Hey Chris, I just stumbled across this post and thought I’d share my approach to the same problem.
See step #”Split Column by Position” below:
let
Source = Folder.Files(“C:\Users\snuss\OneDrive – Company\Desktop”),
#”Added Custom” = Table.AddColumn(Source, “Text.Binary”, each “data:image/png;base64, ” & Binary.ToText([Content])),
#”Split Column by Position” = Table.ExpandListColumn(Table.TransformColumns(#”Added Custom”, {{“Text.Binary”, Splitter.SplitTextByRepeatedLengths(32766), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), “Text.Binary”)
in
#”Split Column by Position”
And the subsequent DAX:
Pics_Aggregated =
ADDCOLUMNS( SUMMARIZE( ‘Pics’, Pics[Name] ), “Full Binary”, CONCATENATEX( ‘Pics’, ‘Pics'[Text.Binary] ) )
Yes, this is more or less what I tried at first. But as I said in my follow-up post, this code reads the files multiple times whereas the code above does not: https://blog.crossjoin.co.uk/2019/05/26/power-query-performance-and-expanding-columns/
Hi Chris
Thanks for the great post! Has helped me a lot! But what If the data I am connecting to already has base64 code in there? How would the code have to be adjusted?
In my case I am connecting to a SharePoint list where I paste the base64 code into a field for each item. I can’t quite figure out how to tweak your code so that the base64 conversion is skipped and instead it just takes the existing column…. Thanks a lot for the help!
The code should be a lot simpler, but without seeing your full query I can’t say for sure how. Please post this as a question to the Power BI forum and send me the link so I can take a look.
Hi Chris
I’ve managed to come up with a solution through the UI. The code now looks as follows:
let
Source = SharePoint.Tables(“URL”, [ApiVersion = 15]),
#”34b325d6-e884-4159-98fe-eb9e9a39aac9″ = Source{[Id=”34b325d6-e884-4159-98fe-eb9e9a39aac9″]}[Items],
#”Removed Other Columns” = Table.SelectColumns(#”34b325d6-e884-4159-98fe-eb9e9a39aac9″,{“Report Name”, “Base64 Thumbnail Ima”}),
#”Split Column by Position” = Table.SplitColumn(#”Removed Other Columns”, “Base64 Thumbnail Ima”, Splitter.SplitTextByRepeatedLengths(30000), {“Base64 Thumbnail Ima.1”, “Base64 Thumbnail Ima.2”, “Base64 Thumbnail Ima.3”, “Base64 Thumbnail Ima.4”, “Base64 Thumbnail Ima.5”, “Base64 Thumbnail Ima.6”, “Base64 Thumbnail Ima.7″}),
#”Changed Type” = Table.TransformColumnTypes(#”Split Column by Position”,{{“Base64 Thumbnail Ima.1”, type text}, {“Base64 Thumbnail Ima.2”, type text}, {“Base64 Thumbnail Ima.3”, type text}, {“Base64 Thumbnail Ima.4”, type text}, {“Base64 Thumbnail Ima.5”, type text}, {“Base64 Thumbnail Ima.6”, type text}, {“Base64 Thumbnail Ima.7″, type text}}),
#”Unpivoted Columns” = Table.UnpivotOtherColumns(#”Changed Type”, {“Report Name”}, “Attribute”, “Value”)
in
#”Unpivoted Columns”
I am guessing this is not that efficient, but it works for now. Any tricks on how to make this simpler?
Hi Chris
I managed to come up with a code through the UI and it looks as follows:
let
Source = SharePoint.Tables(“URL”, [ApiVersion = 15]),
#”34b325d6-e884-4159-98fe-eb9e9a39aac9″ = Source{[Id=”34b325d6-e884-4159-98fe-eb9e9a39aac9″]}[Items],
#”Removed Other Columns” = Table.SelectColumns(#”34b325d6-e884-4159-98fe-eb9e9a39aac9″,{“Report Name”, “Base64 Thumbnail Ima”}),
#”Split Column by Position” = Table.SplitColumn(#”Removed Other Columns”, “Base64 Thumbnail Ima”, Splitter.SplitTextByRepeatedLengths(30000), {“Base64 Thumbnail Ima.1”, “Base64 Thumbnail Ima.2”, “Base64 Thumbnail Ima.3”, “Base64 Thumbnail Ima.4”, “Base64 Thumbnail Ima.5”, “Base64 Thumbnail Ima.6”, “Base64 Thumbnail Ima.7″}),
#”Changed Type” = Table.TransformColumnTypes(#”Split Column by Position”,{{“Base64 Thumbnail Ima.1”, type text}, {“Base64 Thumbnail Ima.2”, type text}, {“Base64 Thumbnail Ima.3”, type text}, {“Base64 Thumbnail Ima.4”, type text}, {“Base64 Thumbnail Ima.5”, type text}, {“Base64 Thumbnail Ima.6”, type text}, {“Base64 Thumbnail Ima.7″, type text}}),
#”Unpivoted Columns” = Table.UnpivotOtherColumns(#”Changed Type”, {“Report Name”}, “Attribute”, “Value”)
in
#”Unpivoted Columns”
Not that efficient I guess? It works for now, but any hints how to make it more efficient?
Hi Chris, I’m using a SQL Server to store the image file and path, how do I convert them into Base64 on the SQL Server directly? Also, when I connect the PowerBI to an OLAP (cube multidimensional), it doesn’t allows us to change the type of the column or create DAX measure. How do I set that column as IMAGE URL in SQL Server or Analysis Services instead in PowerBI? Really need your help, thanks!!
Hi Chris,
If I use the “table or matrix visual” to show the image it works!, but If I use a different custom visual it does not work. I tried the custom visuals “Simple Image”, “Clichet Slicer”, “Image Grid” and nothing. (besides that it looks image by cloudscape does not exist anymore =/ )
Do you have ideas about what is happening?
Do you think there is now a better way to show images in Power BI?
This one is epic………… it really helped me a lot. A perfect workaround for images embedding
Hey Chris,
Like everyone else, thank you. I have it working nicely, however, I am stuck on how to set a “default” image when nothing in my report is selected/focused/sliced. Since there is no active “Name” to associate with until I click on a criteria, my image viewer defaults to a broken icon. Is there a way to set a default image or force a certain index of the image list to be shown when in default view?
Hello Chris,
is there a simple example to be downloaded for going though and analizing the solution? Thank you.
Beacuse I was able to create the table, but the “Image URL” filed cannot be filled with a measure (the “Display Image” measure).
The measure “Display Image” cannot be dropped into “Image URL” field of the “Simple Image” widget. Why?
It worked well until the “Image by cloudscope” has been removed from PowerBI. Now, I cannot find an alternative. The “Simple Image” visual does not accept measures as an input, and putting the image into a table works (quite unexpectedly), but I cannot scale the size. In a table it is much to small.
I know, it’s a shame that visual is no longer available. Various alternative visuals are being worked on…
Can you please specify the alternative visuals for “Image by cloudscope” to display the image. “Simple image” visual is not accepting the measure as an input.
Has there been a new app released that works with this measure?
Great workflow and description of the problem/solution. I’m just bummed they took away the visual with which it works. Having a hard time finding a workaround to point to local files.
Hi, Chris. I’m attempting to do this with PDF files and I have been successful bringing in the initial table with the index column split into multiple rows. And the measure works perfectly; of course, though, there isn’t a PDF data category, and the PDF Viewer custom visual appears to require a column.
So, I wonder if you have attempted to use CONCATENATEX to assemble the Base64 strings for each file into a calculated table with a row per file. I’m not certain in Simon’s response above whether that is what he was attempting to do, and as you indicated, it would result in multiple reads.
Do you have an example of such an attempt? I’ll be trying to conjure the correct expression in the meantime. Thanks!
Hi Chris! This works great for me! However, my images are displaying sideways. It looks like they need to be rotated 90 degrees clockwise and the source images are correct. Any suggestions?
Hi! Amazing solution 🙂 I would like to know wich visual you used. I wasn’t able to find this icon in my PBI 🙁
Thanks
Pingback: Exporting images from OneDrive and embedding in Power BI – Melvin's BI talking
Hi Chris,
That’s a lovely solution however, when I export it from service as ppt, Power BI fails export it as an image giving an error saying this feature is currently not available. Is there any workaround to get the base64 images into the original png image which is directly supported in the powerbi?
are there any visuals currently handling these large base64 images to your knowledge?
Hello Chris,
Thank you, it works like a charm. I’m using Power bi report builder with a power bi dataset but I’m not able to concatenate all the different fields in report builder like in your measure. Is there a simple way to do that ?
Thank you for your help
Pingback: How to use Power BI Parameters in DAX and Power Query - Marquee Insights
Hi Chris,
Thank you for your knowledge and for sharing.
What is the best way to get 15.000 images from SQL database, to show on powerbi service without unnecessary delays and automatic refresh problems (due to dynamic data sources).
I tried the solution from your previous post, but the I had problem with the automatic refresh and with the speed.
Do you have aby suggestion?
Thank you
Hi Chris,
Thanks for the info. For me it is erroring at ‘ConvertAllFiles’
Expression.Error: We cannot convert a value of type Table to type Binary.
Details:
Value=[Table]
Type=[Type]
Any tips on this?
This was such a great post, super clear. Solved my problem perfectly. Thank you, Chris
Did you have success with pdfs or just pictures?
Pingback: Working with images in Power BI. – Tech……Answers
Great solution for pretty common problem, thank you Chris. For me works for pictures, but is not working for pdfs, which is what I really need. Both visuals from Cloudscope work fine for pictures (not pdfs :(), but Pdf viewer visual just does not accept measure. Any ideas why?
Hello
For your images, Vlad, what visual do you use?
John
I put 3 custom visuals – 2 from Cloudscope and pdf viewer (all from Microsoft Store).
Thank you Vlad for replying. Which two from Cloudscope?
Image and Image Pro
Hi Chris, thanks so much for this post. I am quite new at PowerBi and not so experienced in the datamodelling part, but following your steps, I was able to read a folder with images, get the bas64 table and display the images correctly using a slicer. My problem however, is that I am making report cards for schools in Cambodia in which they get a score and hints to improve on 4 indicators. Every indicator can have 4 values and there is an image with score and improvement tips for every value of the 4 indicators. So a reportcard consists of 4 images and every image is a possible of 1 out of 4. So I would like to automate this, instead of using a slicer. I have a construction, in which I read the indicator score and link that to a calculated column in my datafile with the filename. My problem is now how to link that column to the display_measure you have given in order to automatically display the right image. Do you have any suggestions? Thanks in advance
Having a sample would help – can you share it (with dummy data)? I understand that you are trying to avoid using 4 slicers (correct me if I am wrong).
It sounds like you can have 16 images that cover all combinations for 4 by 4 matrix – this way you need just one slicer.
Dear Chris, Vlad
Thanks so much for your quick reply and your willingness to look into the matter. I hope it will be only a small issue, but it is too complicated for me.
A quick background: I am working on a project in which we support all about 9400 schools in Cambodia in improving their water, sanitation and hygiene situation. Better facilities lead to healthier kids and healthier kids skip less classes because they have fewer sick days etc. One of the ways to monitor progress is that the schools fill out a monitoring form every year with about 50 questions on their water, toilets etc. These questions are lumped into indicators on water, toilets, handwash facilities and environmental management. All of these indicators and the overall school performance are rated from 0 to 3 stars.
As an incentive and recognition tool, the schools get a report card which shows their star levels and a certificate. On the report card it shows their star level per indicator and what needs to be done in order to grow in their performance. The report card looks like this and all graphic elements are images that are dynamically loaded depending on the indicator level.
The idea is that at a provincial level or district, people from the Ministry of Education can generate and print out these report cards and discuss them with the schools. Based on the success we had in the Philippines, we decided to do this PowerBI. In the Philippines we could use powerBI to distribute the dashboard online, but sadly that does not work in Cambodia. So the idea is to do a pilot first where a selection of people will install the free version of PowerBi and we will share the data with them using USB. What I have understood is that internet is intermittent at best and they can not spend long times on line, so all data, including the images, need to be on the local computer.
I have made a dashboard in which people can select the geographic area (province, district, village, commune) and then select the name of the school they are interested in and then the correct records are selected. This then generates the report card above and a second page which is the certificate.
My workflow is as follows:
I read an excel file with the monitoring data into PowerBI
I created two subfolders one with support tables etc in excelformat and a second folder with all images
Using the command, I add the columns to the data which contain the filename of the image to be shown column [ladder image]: with values like 00-star-ladder-khmer.png, 01-star-ladded-khmer.png etc.
Using the code sample you showed in you blog, I read all the images and generated a base64 table with the images. File names in the column [Name] with values like 00-star-ladder-khmer.png, 01-star-ladded-khmer.png etc.
So far so, good and using a slicer I can also display the images using this measure:
Display Image =
IF(
HASONEVALUE(‘Base64 images'[Name]),
“data:image/jpeg;base64, ” &
CONCATENATEX(
‘Base64 images’,
‘Base64 images'[Pic],
,
‘Base64 images'[Index],
ASC)
)
However in order to make this work automatically, I need to link up the calculated columns from my main table with the image filename in in the base64 table and then with a measure to display the image. Or, to make it match this example:
How do I relate the contents of column [ladder_image] (00-star-ladder-khmer.png) in my main table to the [NAME] (00-star-ladder-khmer.png) in the Base 64 table and the use the measure to display the correct image. And then of course, repeat for the other indicators as well ..
I hope that this makes thing clear and that it makes sense. Thanks in advance for your time and willingness and please do not hesitate to contact me if you would need more info.
Ubo
Hello Chris, thanks for this amazing content again! I was able to implement the M query successfully.
However, I got an error when I dropped the ConcatenateX measure in the Image visual for a total string length of only ~73,000 characters:
” Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed. ”
I tried running my ConcatX measure with DAX Studio and I’m getting the exact same message. I have not found any useful information online about it. I ran a test modifying the measure to apply LEFT( [ConcatenateX], 30000) and it gets rids of the error, but of course, this truncates the image.
Hope you can shed some light on this, thank you!
Hi Chris
I have tried the code provided in this post to view PDF files in the PDF Viewer. However the DAX output is blank
Display Image =
IF(
HASONEVALUE(‘Test PDF Folder'[Name]),
“data:image/jpeg;base64, ” &
CONCATENATEX(
‘Test PDF Folder’,
‘Test PDF Folder'[Pic],
,
‘Test PDF Folder'[Index],
ASC)
)
I am using similar approach for PNG images, but I have failed to show to image in any other visualization except as a miniature version in a table.
Card, multi-row card, Chiclet Slicer 1.6.3, Image Grid, CloudScope 2.0.5 have all failed to display the image. Is it because of PNG format or do I have a defect somewhere?
Meta data: “data:image/png;base64, “
Curious to know if you found a resolution for using PNG in Chiclet Slicer 🙂
Pingback: Images and custom shapes in Deneb - EXPLORATIONS IN DATA STORYTELLING WITH POWER BI