Storing Images in a PowerBI/Analysis Services Data Models

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.

PowerBI_Images_Stored_Sample

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:

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”:PowerBI_Image_URL_Base64

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!

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

  1. Pingback: Embedding Images in Power BI using Base64 – Some Random Thoughts

    • 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

      • 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)

  2. Pingback: Reutilizar funciones en Power Query – Power BI y Business Intelligence

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

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

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

    • 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

  6. Pingback: Importar imágenes a Power BI – Power BI y Business Intelligence

  7. Pingback: Import images in Power BI from local sources | Online Coding

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

    • 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

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

  10. Pingback: Use SVG Images in Power BI: Part 3 | DataVeld

    • 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?

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

    • 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

  12. Pingback: Visualizations in Power BI Dashboard. Layered visuals; image tiles.

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

    • 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

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

    • 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

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

      • 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?

      • 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?

  16. Pingback: Storing Large Images in Power BI – Curated SQL

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

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

        • 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

          • 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

            • 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

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

  19. Pingback: Powering Up MYOB’s New Foreign Currency functionality(Part 2) – Five Ledger

  20. Pingback: Chris Webb's BI Blog: Storing Large Images In Power BI Datasets Chris Webb's BI Blog

  21. Pingback: Powering Up MYOB’s New Foreign Currency functionality(Part 2) – Five Ledger

  22. Pingback: Showing Images in Power BI – Curated SQL

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

    • 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

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

  25. Pingback: Working with images in Power BI. – Tech……Answers

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

    • 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

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

    • 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

  28. Pingback: Exporting images from OneDrive and embedding in Power BI – Melvin's BI talking

  29. Pingback: 在PowerBI中加载图片 -- 存储法 - 算法网

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

    • 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

Leave a Reply

Your email address will not be published. Required fields are marked *

*