Our last blog discussed the pros and cons of embedding images in the Power BI report with URLs. Now I will guide you on embedding images in the Power BI report by reading binary data from the source.
Binary Data to Base64
I will again take the example of SharePoint to read binary data for images. We can use any source system that has a binary of the images. It could be SQL Server, Azure Storage, or any semi-structuredRaw data. We only need binary content in the Power BI data model. Let’s get started.
- Connect your Power BI with SharePoint List or Folder
2. Load binary data from SharePoint to Power BI
3. Now we have complete binary of images in the Power BI data model, and whatever the size of the image, Power Query will fetch all data of the image.
4. Create a duplicate column for Content (if you want to keep the original content in the data model)
5. Change the name and type of duplicate column.
Name: Image To Text
As a result, we will have the binary content in the text. By default, Power BI does not load binary data in the data model as it would be an additional load on the model if it cannot be used. Therefore, change it to text type. After that, close the Power Query Editor.
6. Now we have data in Base64 format, and we also need to add metadata with the text values so that the Power BI engine considers it as Base 64. Create new calculated column “Image” and append “data:image/jpeg;base64,” text with “Image to Text” Column values. Also, change the column type to “Image URL.”
7. Now put “Image” column in the grid with the image name
You will see that Power BI will render images in cells. Data referenced for these images is completely indigenous and resides inside the Power BI data model. We can export reports with images into PDF, Excel, and PowerPoint. This looks great but it has some limitations.
In Power Query, when we convert images Binary to Text, we cannot store more than 32,677 characters in Text column type. Power Query will convert as many binaries as possible into text that is under the character limit mentioned earlier, and all the remaining image data will not be loaded into the data model.
In this article, we have converted the binary content of images into a Base64 string and displayed the images in the report; nonetheless, there was an issue with this approach. We cannot embed large images as the Base64 string will exceed the Text column character limits. We will find out a workaround for this problem in our next blog.