Storing Images in a PowerBI/Analysis Services Data Models

Posted on 2018-01-05 by Gerhard Brueckl — 73 Comments ↓

As some of you probably remember, when PowerPivot was still only available in Excel and Power Query did not yet exist, it was possible to load images from a database (binary column) directly into the data model and display them in PowerView. Unfortunately, this feature did not work anymore in PowerBI Desktop and the only way to display images in a visual was to provide the URL of the image which is public accessible. The visual would then grab the image on-the-fly from the URL and render it. This of course has various drawbacks:

The image needs to be available via a public URL (e.g. upload it first to an Azure Blob Store)
The image cannot be displayed when you are offline
The link may break in the future or point to a different image as initially when the model was built

There is also a feedback items about this issue which I encourage you to vote for: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7340150-data-model-image-binary-support-in-reports

Until today I was sure that we have to live with this limitation but then I came across this blog post from Jason Thomas aka SqlJason. He shows a workaround to store images directly in the PowerBI data model and display them in the report as if they were regular images loaded from an URL. This is pretty awesome and I have to dedicate at least 99.9% of this blog post to Jason and his solution!

However, with this blog post I would like to take Jasons’ approach a step further. He creates the Base64 string externally and hardcodes it in the model using DAX. This has some advantages (static image, no external dependency anymore, …) but also a lot of disadvantages (externally create the Base64 string, manually copy&paste the Base64 string for each image, hard to maintain, cannot dynamically add images …). For scenarios where you have a local folder with images, a set of [private] URLs pointing to images or images stored in a SQL table (as binary) which you want to load into your PowerBI data model, this whole process should be automated and ideally done within PowerBI.

Fortunately, this turns out to be quite simple! Power Query provides a native function to convert any binary to a Base64 encoded string: Binary.ToText() . The important part to point out here is to use the second parameter which allows you to set the encoding of the resulting text. It supports two values: BinaryEncoding.Base64 (default) and BinaryEncoding.Hex. Once we have the Base64 string, we simply need to prefix it with the following meta data: “data:image/jpeg;base64, “

To make it easy, I wrote to two custom PowerQuery functions which convert and URL or a binary image to the appropriate string which can be used by PowerBI:

let
    UrlToImage = (ImageUrl as text) as text =>
let
    BinaryContent = Web.Contents(ImageUrl),
    Base64 = "data:image/jpeg;base64, " & Binary.ToText(BinaryContent, BinaryEncoding.Base64)
in
    Base64
in
    UrlToImage

let
    BinaryToPbiImage = (BinaryContent as binary) as text=>
let
    Base64 = "data:image/jpeg;base64, " & Binary.ToText(BinaryContent, BinaryEncoding.Base64)
in
    Base64
in
    BinaryToPbiImage

If your images reside in a local folder, you can simply load them using the “Folder” data source. This will give you a list of all images and and their binary content as separate column. Next add a new Custom Column where you call the above function to convert the binary to a prefixed Base64 string which can then be displayed in PowerBI (or Analysis Services) as a regular image. Just make sure to also set the Data Category of the column to “Image URL”:

And that’s it, now your visual will display the image stored in the data model without having to access any external resources!

Caution: As Jason also mentions at the end of his blog post, there is an internal limitation about the size of a text column. So this may cause issues when you try to load high-resolution images! In this case, simply lower the size/quality of the images before you load them.
UPDATE May 2019: Chris Webb provides much more information and a solution(!) to this issue in his blog post: https://blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets

Download: StoreImageInPbiModel.pbix
This PowerBI Desktop model contains all samples from above including the PowerQuery functions!

73 Replies to “Storing Images in a PowerBI/Analysis Services Data Models”

Pingback: Embedding Images in Power BI using Base64 – Some Random Thoughts
Ola on 2018-01-07 at 00:25 said:

PowerBI can store Max is 32.766 char.
Allowed image types: https://www.iana.org/assignments/media-types/media-types.xhtml#image

Reply ↓
- Sonal on 2020-08-04 at 14:21 said:
  
  Hi Gerhard,
  
  I tried using the below, however, I am getting Syntax error on data:image/jpeg
  
  I am a newbie in PowerBI, would you pl let me help me with the reason behind this error.
  
  let
  BinaryToPbiImage = (BinaryContent as binary) as text=>
  let
  Base64 = “data:image/jpeg;base64, “ & Binary.ToText(BinaryContent, BinaryEncoding.Base64)
  in
  Base64
  in
  BinaryToPbiImage
  
  Reply ↓
  - Gerhard Brueckl on 2020-08-05 at 17:21 said:
    
    the double quotes look a bit weird but it seems this is also the case for the sample-code I provided
    
    can you change them from “ to ”
    
    (I also updated the code samples in the blog post)
    
    Reply ↓
Pingback: Reutilizar funciones en Power Query – Power BI y Business Intelligence
Luke McCarthy on 2018-01-16 at 00:54 said:

Is there a way to display conditional images for reports that are built from a streaming pushed dataset?
I have no power query or modelling capabilities.
So I cannot change a column’s data category to Image URL.
Is there another way to do this?

Thanks

Reply ↓
- Gerhard Brueckl on 2018-01-16 at 21:10 said:
  
  well, there are several things to say about streaming/push datasets here:
  – if you are using pure Streaming datasets, there is no dashboard-visual which would actually display an image, so this does not make much sense
  – for Push-Streaming and Push datasets, you can push the prefixed Base64 string into a column and set the data category of the column to ImageUrl. This is not very well described but when you create the dataset using the REST API (or the C# wrapper that I once wrote http://wordpress.gbrueckl.at/2017/05/c-wrapper-power-bi-rest-api-version-2/) you can set the the data category very easily.
  a lot of this is also documented here: https://powerbi.microsoft.com/en-us/blog/newdatasets/
  
  kind regards,
  -gerhard
  
  Reply ↓
sam on 2018-01-20 at 16:15 said:

I was earlier doing this by storing the images on One Drive Personal and making the folder public.
This method is fantastic. I guess this should work for a network folder as well and hence can be used in a corporate environment.
Thank you Jason and Gerhard

Reply ↓
Kyle on 2018-01-25 at 07:14 said:

Is there anyway within Power BI to reduce the image size/quality within the Query Editor? Following your blog we were able to get this to work for images in our database, but for larger images we hit the max character limit of 32,766, and these images doesn’t display fully in visualisations.
Interestingly this seems to be a restriction only once the data is loaded in the data model. In the Query Editor, the full base 64 string shows, therefore if there is a way to compress the image in the Query Editor before loading it, that should work.
Apologies if this is a naive question, still relatively new to Power BI.

Reply ↓
- Gerhard Brueckl on 2018-01-25 at 11:06 said:
  
  I am not aware of any Image processing capabilities within PowerBI/PowerQuery so I only see these options:
  – reduce image size/quality before loading the images
  – use a R datasource and do the image transformation there (this may cause issues with automated refreshes though)
  – find/write a webservice/API which can process your image on-the-fly and return it to PowerBI/PowerQuery
  
  -gerhard
  
  Reply ↓
  - Kyle on 2018-01-28 at 23:15 said:
    
    thanks for the tips!
    
    Reply ↓
  - Andy on 2018-09-17 at 16:20 said:
    
    Thanks for this Gerhard. Could you explain how you could do the image transformation in R? I’m not able to reduce image size/quality so need another way to get the string length down.
    Thanks
    
    Reply ↓
    - Gerhard Brueckl on 2018-09-17 at 16:55 said:
      
      Hi Andy,
      I am by for not an R expert but I am quite sure that there are some R-libraries that allow you to scale an image down.
      Just google for it – here is something that I could find: https://stackoverflow.com/questions/35786744/resizing-image-in-r
      
      regards,
      -gerhard
      
      Reply ↓
Pingback: Importar imágenes a Power BI – Power BI y Business Intelligence
Pingback: Import images in Power BI from local sources | Online Coding
Elias on 2018-02-23 at 15:59 said:

This is a fantastic approach! Thanks for sharing

Reply ↓
Chris on 2018-04-24 at 18:01 said:

This works great but the images seem to come back with a static small width regardless of the original image size (and all image with the same width even if the sources are different sizes). Is there a way to get larger images?

Reply ↓
- Gerhard Brueckl on 2018-04-25 at 09:21 said:
  
  The images are loaded as they are originally as we simply encode their binary content.
  The display of the image however may vary from visual to visual. Most of the visuals scale the image(s) so it fits the size of the visual
  
  Reply ↓
Jonatan on 2018-05-10 at 22:41 said:

Hello.

Is there a way in PowerBI to add dynamic images from a local folder on mass that are bigger than 30KB? I followed your method but there’s a limitation for the images size.

Reply ↓
- Gerhard Brueckl on 2018-05-14 at 09:30 said:
  
  Hi Jonatan,
  
  there is a hard limit on the size of text-field (32766 characters) that can be stored in PowerBI. As long as PowerBI does not support larger sizes for text-fields or binary fields, I am afraid you have to convert the images manually to fit the limits.
  If you want to do this on the fly (during the import) you may take a look at R scripts and/or web-APIs that could possibly do this for you
  
  -gerhard
  
  Reply ↓
  - MarcV on 2018-12-06 at 03:29 said:
    
    To overcome the image size / 32766 char limit:… Using Query Editor, split the text into rows by number of characters (32766). Add an index just to make sure you still get the right sequence. And then just reassemble in DAX using CONCATENATEX. I was successful in loading higher res images, no need for R or APIs!
    
    Reply ↓
    - Gerhard Brueckl on 2018-12-06 at 10:24 said:
      
      would you like to go into more detail about this approach?
      you split the binary value into smaller junks (<32766 bytes) and create a table that has one row for each junk. In DAX you create a calculated table using CONCATENATEX() to combine them again into a single value - is that correct?
      
      Reply ↓
      - Ruggiero Lauria on 2019-04-05 at 12:26 said:
        
        Well I have tried it, but I have some concerns about relationship and cardinality.
        By the way you can split the field by num of char in columns (no problem with the cardinality, but unpredicabile number of columns) or by rows and then reconstructe it in DAX, with a simple column or row aggregation:
        Have a look here:
        https://app.powerbi.com/view?r=eyJrIjoiODc4YzM0NTktNTRjZi00NGFhLWFjNmQtZmQ1OTNhOWJkNmU2IiwidCI6IjA3ODk3NTU2LWU3ZDItNGQzMC04Y2U3LTE5NDdkMWQyM2FkMyIsImMiOjh9
        
        Reply ↓
    - CS on 2018-12-19 at 13:02 said:
      
      If you could share your solution for this, that would be great. I’ve been wrestling with this problem for a while.
      
      Reply ↓
      - Gerhard Brueckl on 2019-05-20 at 17:39 said:
        
        https://blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets
        
        Reply ↓
Pingback: Use SVG Images in Power BI: Part 3 | DataVeld
Brad on 2018-07-21 at 14:01 said:

Thanks for this tutorial – it worked a treat.

Reply ↓
Davi Augusto Felicio Damasceno on 2018-07-23 at 16:07 said:

Hi, Thanks

But my images are being cropped

https://ibb.co/e3FZZJ

My database is firebird

Reply ↓
- Gerhard Brueckl on 2018-07-23 at 17:09 said:
  
  This can not really happen during the import. If something goes wrong there, you would see an error instead of the image.
  Two other possible causes of cropping:
  – Issue with the PBI visual
  – issue with the original image
  
  Can you check this?
  
  Reply ↓
amalsp on 2018-10-23 at 08:26 said:

Thank You.

Reply ↓
Yassaui on 2018-11-21 at 12:55 said:

Hello, Gerhard! Thank you for this great solution!

I wanted to ask about using images in other visuals like Card Browser. Do not know why, it does not accept the image in base64. Firstly, why does not it work for this visual, and secondly, is there any workaround? Thank you in advance!

Reply ↓
- Gerhard Brueckl on 2018-11-21 at 15:42 said:
  
  Hi Yassaui,
  
  I just did some tests and the Card Browser also does not work with a regular image accessed via URL directly (without storing)
  so from my point of view the issue is related to the actual visual and if it is capable to display an image at all – the Card Browser visual obviously does not support showing images
  
  I would advice to contact the author of the visual if you want it to display images correctly
  
  regards,
  -gerhard
  
  Reply ↓
  - Yassaui on 2018-11-22 at 05:25 said:
    
    Thank you.
    
    Reply ↓
Pingback: Visualizations in Power BI Dashboard. Layered visuals; image tiles.
samira ab on 2018-12-11 at 12:13 said:

Hello, Gerhard and thank You,
I want to show images in power bi that are stored in binary format in SQL column. My connection in power bi is live ( Read data From Cube) . IS there any solution for this?

Reply ↓
- Gerhard Brueckl on 2018-12-11 at 14:48 said:
  
  Hi Samira,
  
  in theory it should be possible if you convert the binary value of your SQL column into an appropriate string that contains the prefix and Base64 encoded value as I do in M.
  I would probably do that in a View. and set the Live connection top of that view
  
  regards,
  -gerhard
  
  Reply ↓
Harm Horstman on 2019-01-21 at 11:53 said:

Hi Gerhard, we want to show images stored in our Azure File Storage in one of our Power BI dashboards, without the need of converting and storing them in a binary column. The idea is to show a list of image names in a grid and show the image on demand for the selected row in a tooltip or a detailed visual. Do you have any experience with this? Would this be possible using out of the box PowerBI features?

Reply ↓
- Gerhard Brueckl on 2019-01-21 at 13:10 said:
  
  Hi Harm,
  
  sure, thats a very native functionality. PowerBI can display images from any public URL and storage accounts can be configured to allow public access.
  for example, I have a blob storage account called “gbpublic” with a container with access policy “files” which can be accessed by anyone:
  https://gbpublic.blob.core.windows.net/files/img.png
  this link can be used e.g. in PowerBI to display images
  you can also use Shared Access Signatures (SAS) for your blob account for enhanced security
  
  kind regards,
  -gerhard
  
  Reply ↓
Matt on 2019-02-27 at 06:57 said:

Hi Gerhard, great work! To confirm the query earlier about Data Cards and using the suggested method, are you suggesting Data Cards only allow a public URL to source the image file and not this new approach? Thanks !!

Reply ↓
- Gerhard Brueckl on 2019-02-27 at 09:09 said:
  
  according to my previous research, Data Cards in general do not work with images, regardless how they are implemented
  
  Reply ↓
  - Dima on 2019-08-14 at 15:07 said:
    
    Hi Gerhard. First of all – thanks a lot for this post.
    You say: “Data Cards in general do not work with images, regardless how they are implemented”. But 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?
    
    Reply ↓
  - Dmitry Pi on 2019-08-14 at 15:08 said:
    
    Hi Gerhard. First of all – thanks a lot for this post.
    You say: “Data Cards in general do not work with images, regardless how they are implemented”. But 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?
    
    Reply ↓
    - Gerhard Brueckl on 2019-08-14 at 18:00 said:
      
      Did you load the images using the approach described in the post? Convert to base64 etc?
      
      Reply ↓
Pingback: Storing Large Images in Power BI – Curated SQL
Axel on 2019-05-31 at 19:13 said:

In this statement –> BinaryEncoding.Base64 (default) and BinaryEncoding.Hex. Once we have the Base64 string, we simply need to prefix it with the following meta data: “data:image/jpeg;base64, “

How to tell it is using Base64 or Hex ? and if it is Hex, would the prefix will be
“data:image/jpeg;Hex” ?

Thanks in advance

Reply ↓
- Gerhard Brueckl on 2019-05-31 at 19:37 said:
  
  You find these detail in the docs of Power Query which is also linked.
  I have not tested this with hex encoding but I would guess it does not work
  
  Reply ↓
  - Axel on 2019-06-02 at 10:17 said:
    
    Noted with thanks. One last question, as there is a confirmation of the string length (< 32766), the outcome if it exceed that length will be the image crop, is it?
    
    So if my outcome is NOT my image (which is crop) but a system icon like a tear paper, then it's not about the string but I'm not doing it correctly ?
    
    Thanks again.
    
    Reply ↓
    - Gerhard Brueckl on 2019-06-02 at 11:32 said:
      
      Yes, if it is a problem with the string length the image will be cropped/incomplete. Also check out Chris Webb’s post that I linked recently at the end of my post when you want to load larger images
      
      Reply ↓
      - Axel on 2019-06-03 at 19:02 said:
        
        Thank you for your confirmation and advice.
        
        Allow me to justify one more time, this method, will it works if I’m using Direct Query ?
        
        Bcos I try many time with Direct Query, the image not showing instead it is forever loading, but when I change to Import, it is showing instantly.
        
        Thanks again
        
        Reply ↓
        
        Gerhard Brueckl on 2019-06-04 at 09:18 said:
        
        I have to admit that I have not tried using this approach with Direct Query but it *should* behave just as any other regular column that is retrieved from the source and then visualized in Power BI.
        Please keep in mind, you need to do all the processing described in this post (e.g. prefixing with ‘data:image/jpeg;base64,’, convert to Base64, …) in a SQL View
        
        Reply ↓
Alex on 2019-08-23 at 05:19 said:

Many thanks for your post! much appreciated.

Reply ↓
Alexander George on 2019-10-21 at 16:37 said:

Hi,
I tried this for the Table visualization and it worked like a charm.However i tried the same for multi row card option and it started showing the image url as text instead of the image. Any idea on how to resolve this?

Another issue i faced is when exporting to Pdf ,it kept saying internal error and the pdf wasnt generated

Reply ↓
- Gerhard Brueckl on 2019-10-22 at 06:42 said:
  
  Hi Alexander,
  This was already discussed in another comment. The multi-row card visual also does not work with regular URL images so it will also not work with stored images.
  
  -gerhard
  
  Reply ↓
- Gerhard Brueckl on 2019-10-22 at 06:43 said:
  
  Regarding the PDF export I need to investigate.
  Does this one work with regular URL images?
  
  Reply ↓
Pingback: Powering Up MYOB’s New Foreign Currency functionality(Part 2) – Five Ledger
Pingback: Chris Webb's BI Blog: Storing Large Images In Power BI Datasets Chris Webb's BI Blog
Pingback: Powering Up MYOB’s New Foreign Currency functionality(Part 2) – Five Ledger
Pingback: Showing Images in Power BI – Curated SQL
Guilherme Matheus on 2020-06-03 at 04:52 said:

Hi Gerhard, you were the only one that I found that mentioned the Analysis Services, and I’ve got some questions. 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!!

Reply ↓
- Gerhard Brueckl on 2020-06-03 at 15:24 said:
  
  well, the Image URL property is a setting in Power BI (or Analysis Services Tabular) so there is no way to set this setting in SQL Serer or Analysis Services Multidimensional
  if the image is stored in a column in your SQL table, you can use the provided sample .pbix to load those directly into PowerBI
  
  Reply ↓
Pietro on 2020-06-24 at 16:37 said:

Hello Gerhard, thank you for the solution. I’m interested into the finetuning by “Storing Large Images In Power BI Datasets” blog you posted. Can you create a dowloadable example with the higher image size solution proposed in that blog page? Thank you.
Pietro

Reply ↓
- Gerhard Brueckl on 2020-06-24 at 17:08 said:
  
  did you have a look here?
  https://blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets/
  
  Reply ↓
Pingback: Working with images in Power BI. – Tech……Answers
Bavya on 2020-08-01 at 20:29 said:

If i use the Binary code to get the images into the Power BI and if publish the Pbix file into the Workspace, So to refresh the dataset, Is it required Manage gateways ?

Please Help

Reply ↓
- Gerhard Brueckl on 2020-08-01 at 21:34 said:
  
  It works just like any other refresh. If it is accessible from the PBI service, the refresh will work. There is nothing special about it compared to other refreshes
  
  Reply ↓
Neil McLeod on 2020-09-24 at 03:32 said:

Hi Gerhard, do you know if this can be done in the Paginated Report Builder? I tried and failed, think it is because of the Image URL Data Categorisation in PowerBI – something report builder can’t seem to do.

Reply ↓
- Gerhard Brueckl on 2020-09-24 at 08:29 said:
  
  to be honest I do not know how or if this can be done in Power BI Report Builder as I have never used it.
  For classic SQL Server Reporting Services I was using dynamic images which were also loaded on the report server but I do not know if you could do something similar in your scenario
  
  -gerhard
  
  Reply ↓
Pingback: Exporting images from OneDrive and embedding in Power BI – Melvin's BI talking
Pingback: 在PowerBI中加载图片 -- 存储法 - 算法网
Informatika on 2023-08-12 at 07:10 said:

How can the process of automating the conversion and loading of images into a PowerBI data model, especially from local folders, URLs, or a SQL table, be improved beyond the manual Base64 string approach discussed in the blog post?

Reply ↓
- Gerhard Brueckl on 2023-08-29 at 21:10 said:
  
  what exactly are you looking for? what do you think needs to be improved?
  the general idea of this approach is quite simple – store the image as part of the datamodel which as of now is only possible the way I showed in the blog post
  
  Reply ↓
Robert on 2024-06-11 at 23:01 said:

When I try to do this with images hosted on SharePoint, I get an error saying at the Web.Contents step saying “DataFormat.Error: The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part).”

Has anybody encountered this before? Is this some sort of security issue on my company’s side?

Reply ↓
- Gerhard Brueckl on 2024-06-19 at 08:51 said:
  
  depending on how you load the images from sharepoint, this maybe help:
  https://blog.crossjoin.co.uk/2022/11/28/web-contents-text-concatenation-and-dataset-refresh-errors-in-power-bi-power-query/
  
  Reply ↓

Share this:

73 Replies to “Storing Images in a PowerBI/Analysis Services Data Models”

Leave a Reply Cancel reply