This week a colleague asked me if images from Business Central (formerly Dynamics NAV) could also be displayed in a Power BI report and the good news up front, “It works!”
The special challenge here is that Business Central stores the image compressed with additional 4 bytes in a Blob data type (Binary Large Object) in the SQL database. However, for the display in a Power BI report we need a Base64 image – string, which we use as Image-URL.
Thumbnails of the images are stored in the Business Central SQL database in the table “Tenant Media Thumbnails”. We use these smaller images in order to not unnecessarily inflate the dataset and to avoid character length limitations (32766 characters) in Power BI as much as possible.
Generate Data-URI in Power Query
Using a Custom Column, we then perform the following transformations. (read from the inside to the outside)
- Remove the first 4 bytes from the Content column
- Decompress the result
- Convert the decompressed binary result to Base64 text
- Concatenate the Base64 text together with the Mime Type to a Data-URI
= "data:" & [Mime Type] & ";base64," & Binary.ToText( Binary.Decompress( Binary.Range([Content], 4) , Compression.Deflate) , BinaryEncoding.Base64)
Image URL in Power BI report
In order for the Base64 image string to be interpreted and displayed as an image by Power BI, the data category of the column must be changed to “Image URL”.
It is recommended to make the change before displaying the column in Power BI, otherwise the visual will render very long when displaying the long text.
2 Gedanken zu „Display images from Business Central in Power BI“
We are looking to fetch the images form BC cloud version, we are not able to fetch the images. We are getting errors in the DAX queries, we have used the same queries as you have suggested.
Could you please guide us, on fetching the images in BC cloud environment.
the code in this blog post is PowerQuery and not DAX. I’m using a SQL connection here to load the data, I haven’t tried loading the images via the API yet.