Load Image File In Power BI From SQL Server

How we can load images into Power BI from SQL Server and display it in our reports

A file can be stored as binary in SQL database. In Power BI, we generally load the text or numeric data, whether it's a date or name or some calculations. Sometimes, we are required to display images on a Power BI report. Although it is not recommended to load the files in a Power BI report, let us look at how to make it possible if necessary.

In this article, we will see how to load image files into Power BI and how we can display it in our report.

Step 1

Open Power BI Desktop, click on “Get Data”.

Step 2

Select the SQL Server Database and click "Connect".

Step 3

Insert the Server Name, Database Name (optional), and select the Data Connectivity Mode; we can import the table into Power BI or we can use the Direct Query mode. Expand the Advance option, insert the SQL Statement/Query using which we will load our table containing image file and click OK.

Step 4

The pop-up window will preview the table with Binary Image column. It will allow us to load the table or edit the query and make the changes and load.

Here, we need to make changes as we want to convert binary to the image; we can see ImageData column has a binary value, hence click "Edit".

Step 6

It will open the Power Query Editor window.

Step 7

Change the data type of ImageData (Binary) column to text from the top panel's Transform tab.

We need to add one more column to the table to convert the text to the image URL format.

Step 8

Go to "Add Column" and click on "Custom Column". It will open a window. Enter the column name and formula given below and click OK.

“data:image/<image_format>;base64,”&[ImageData<binary_column_name>]

Our column is added. Now, click on "Close and Apply" from the Home tab.

Step 9

On Data tab, select Image column and change its data category to Image URL.

Step 10

To use the image in the report, go to the Report tab. Add table visual and select Image column or we can have custom visuals to show the image from the marketplace also.

This is how we can display images in our report. I have also added custom image visual from the marketplace.

Summary

Image file will load in Power BI as Binary column. We need to change the data type binary to text and append the formula to make it image type. We also need to make its data category to Image URL and we are ready to show images in our report.

Note

The better way would be to store the image in a server and load the URL from your dataset. This will reduce the size of your dataset.