How To Use Images Stored In SQL Server Table With Images And Varbinary Datatype In Power BI

Overview

In this article, we will learn how we can display images directly from SQL Server database to Power BI Desktop. In SQL Server, the images can be stored using different datatypes like,

• Image
• Varbinary(max)

In this article, we will display the stored images in form of image or varbinary(max) to Power BI. It is very simple.

For practice, I have uploaded the solution on GitHub. Please refer to the following link for the Solution file (.pbix) and the SQL Script.

• Download here

Let’s get started!

For SQL Server

For example, I have created two sample tables named “ImageSample” and “ImageSample1”. If you refer to the following screenshot,

In the “ImageSample” table, the image is stored in form of Varbinary(max) datatype, while in the “ImageSample1” table, the image is stored in form of Image datatype. Now, let’s perform the steps to show an image in Power BI.

Step 1

From Home menu, click on Edit Queries. The loaded table looks like this.

 

 

Step 2

Select your Image column. In my case, I will select a column named “ImageData” and change its data type from “binary” to “text”.

To change the data type, select your column and go to Transform > From datatype select > Text.

It is converted in Text format.

Step 3

Now, let’s add one custom column.

To add a custom column, go to "Add Column" from the ribbon and click on "Custom Column".

 It will open the popup.

Add the following formula -

1. "data:image/png;base64," &[ImageData]

Click on OK.

The added custom column will look like this.

Step 4

From the ribbon, select Home and click on "Close and Apply".

Step 5

Now, let’s change the data category of the custom column we have created. Select a column named “Custom” and from Data Category, select “Image URL”.

Step 6

Now, let’s drag the column in table visual. It will look like this. You can apply the same in custom third-party Image visual for a better result.

We can also show images from datatype “Image” from SQL Server. You need to perform the same procedure. Download the sample for both examples.

Conclusion

This is how we can show images from SQL Server database to Power BI.

Hope you love this article!

Stay connected with me!