In the meantime, I have already been asked twice in my blog post „Display images from Business Central in Power BI“ whether I could show how to display images from the cloud version of Business Central via API in Power BI.
First of all, this is subject to certain restrictions. In this example, we will convert the binary data of the image into a Base64 image string and then use it as the image URL in Power BI. However, the maximum character length in Power BI is 32,766 characters, which means that larger images cannot be displayed in full. Alternatively, you could also refer to external image URLs, but in this case the URL must be accessible anonymously.
Patrick and Kristian show how „“secured“ images can also be accessed in the articles below.
But let’s start by loading the image data via the Business Central API.
To do this, we open the „Dynamics 365 Business Central“ Connect under „Get Data“.
In the connector, we then select the environment, the company and then the entity items of the Standard APIs v2.0 and click on „Transform data“.
With „Choose Columns“ we reduce the columns to the columns „number“ „dispalyName“ and „picture“ that are relevant for us.
We then expand the „picture“ column to include the „record“ information „contentType“ and „pictureContent“.
The Base64 image string can now be generated via a „Custom Column“.
= "data:" & [contentType] & ";base64,"
& Binary.ToText(
[pictureContent]
, 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.
Show secured image in Power BI
Patrick and Kristian show a way to display images in Power BI that require authentication at the source in their articles. In this case, a Power App is integrated that controls the authentication and displays the images.
Guy in a Cube – How to get Secure Images in Power BI Reports
PBI Guy – Show “secured” Images in Power BI
Great guide! However, am getting stuck with error in power BI whenever I apply changes.
„OLE DB or ODBC error: [DataSource.Error] An error happened while reading data from the provider: ‚A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host“
I have retraced my steps and it seems this happens as soon as I expand the culumn as in your guide
Which connector do you use to access Business Central with Power BI?
This version of the article uses the API interface, but there is also a SQL variant.
https://www.thinkbi.de/2021/12/21/display-images-from-business-central-in-power-bi/
The error message seems to be a SQL driver error.
Thanks for your response! I am using the standard Business Central API as in this guide (which is great btw!). Everything looks perfect in power query. However, when I click „apply“ the error comes. I can apply and refresh everything until I do the step where I expand the picture culumn.
Would gladly pay for support if that is a thing you do.
Thanks!