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:

Function: UrlToPbiImage
  1. let
  2.     UrlToImage = (ImageUrl as text) as text =>
  3. let
  4.     BinaryContent = Web.Contents(ImageUrl),
  5.     Base64 = “data:image/jpeg;base64, “ & Binary.ToText(BinaryContent, BinaryEncoding.Base64)
  6. in
  7.     Base64
  8. in
  9.     UrlToImage
Function: BinaryToPbiImage
  1. let
  2.     BinaryToPbiImage = (BinaryContent as binary) as text=>
  3. let
  4.     Base64 = “data:image/jpeg;base64, “ & Binary.ToText(BinaryContent, BinaryEncoding.Base64)
  5. in
  6.     Base64
  7. in
  8.     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”: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!

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

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

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

Leave a Reply