Display images from Business Central in Power BI

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)

1. Remove the first 4 bytes from the Content column
2. Decompress the result
3. Convert the decompressed binary result to Base64 text
4. 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.