Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mjfulke
Employee
Employee

How to use Images Stored in a SQL Server Table with Power BI

 Power BI has several options to display images in a report or dashboard but they mostly require that the image is a url to a publicly accessible website.    What I'll show you in this post is a way to take images stored in a database and bind them to a table visualization.   This will work with lots of visuals that display images.   My use case was to display chart images generated from R using SQL Server's Machine Learning Server


@Meagan Longoria recently introduced me a great article from @SQLJason Embedding Images in Power BI using Base64.   This gave me all the information I needed to look at solution to bind image data stored in a SQL Server table.   

Definitely read SqlJason's article, paying close attention to limitations etc.  This solution is meant for relatively small images you would show in a report.  If you use it for large images it will likely fail.  Reduce the resolution in those cases.

 

Getting images into SQL Server

 Go to your favorite SQL Server and create a table and insert some images.  Here's my sample code to give you one way you can get images into a database table

 

--CREATE Table Logo
--(
--    LogoId int,
--    LogoName varchar(255),
--    LogoImage image
--)

INSERT INTO dbo.Logo
( 
  LogoId,
  LogoName,
  LogoImage
)  
SELECT  1,'Power BI',  
      * FROM OPENROWSET  
      ( BULK 'C:\Users\me\Desktop\LogoDemo\powerbi.png',SINGLE_CLOB)  as ImageFile

INSERT INTO dbo.Logo
( 
  LogoId,
  LogoName,
  LogoImage
)  
SELECT  2,'Azure',  
      * FROM OPENROWSET  
      ( BULK 'C:\Users\me\Desktop\LogoDemo\azure.png',SINGLE_CLOB)  as ImageFile

INSERT INTO dbo.Logo
( 
  LogoId,
  LogoName,
  LogoImage
)  
SELECT  3,'Microsoft',  
      * FROM OPENROWSET  
      ( BULK 'C:\Users\me\Desktop\LogoDemo\microsoft.png',SINGLE_CLOB)  as ImageFile

 

Get the image data using Power BI Desktop

Now that you have some images in a SQL table, switch over to Power BI Desktop and get the data.
(In Desktop:  Get Data, SQL Server, Login to your SQL Server, and pick your table that stores images. 

2018-03-23_15-21-1.jpg

Change the binary column to Text

 

 2018-03-24_9-51-27.jpg

 

Now create a custom column and append a URI to tell Power BI these are png images.   If using jpeg use jpeg instead of png.

 

2018-03-24_9-52-27.jpg

 

Close & Apply the data.


This is an important step. In the Modeling tab, change the image column ([Logo]) to a  Data Category of Image URL.

 2018-03-24_10-11-07.jpg

 

Now you can use a visuals, like a table, and display your images that came from a SQL Server vs. having to find a way to store images on a public internet site.   

 

2018-03-24_10-17-28.jpg

 

 

I hope this works for you like it did for me.  


 

1 ACCEPTED SOLUTION
18 REPLIES 18
gabrielvigo
Helper I
Helper I

Hi all!

 

I followed all the steps.

But I have issues with the images I upload (from PowerApps to SQL) with the cell phone, as a photo.

 

I uploaded some as png from the PC and those look.

JPEG images are not fully loaded.

 

Screenshot_1.png

admin_xlsior
Post Prodigy
Post Prodigy

Hi guys,

 

Does anyone ever try or know how to do it with Direct Query ?

 

I tried with import and it's works, but when it is comes to Direct Query since it needs change data type on the column, it is not allowed unless I changed it to Import.

 

Anyone know how ?

 

Thanks

 

@admin_xlsior I am not sure if you are still having this problem but what I found is I just created a Veiw within my SQL DB and used Json to convert the images from bianary to Base64Jpeg. The you would just pull the images from the view you created, but I am having a problem with the images being to large for Power BI to render them. 

 

CREATE VIEW TNIPIC_VIEW AS
SELECT BACK
FROM OPENJSON(
(
SELECT CUSTOMER_ID, BACK, CHECKED_IN
FROM (SELECT DBO.YOUR DATABASE AS BACK) T
FOR JSON AUTO
)
)WITH (BACK VARCHAR(MAX))

cymorg
Advocate I
Advocate I

Is it possible to use images stored as binary data  - varbinary(max) - stored in SQL Server?

The length of the encoded data needs to be less than 32766 characters.

mjbernier
Helper I
Helper I

Tried all the steps laid out here, but it didn't work. All I see are either the URLs or the "broken image" symbols. Figuring it might be an image size issue, I reduced the images to less than 100K and still no joy. I checked the Ideas forum and this one was first introduced over THREE YEARS ago with no response from Microsoft. Why do we have to take in-house images sitting in tables and export them to a file server, then link to them in order to display them in an in-house report? It's beyond ridiculous.

I've found that the maximum length a string can have to be processed by Power BI is 32766 characters.

So once you've converted your image to a Base64 string, if the length of the string is greater than this then it will crop the image.

 

 Hi, I tried same step, and count the character is 1024, but still it is display as broken image. My image is png.

 

However by look at the character length, I'm not sure whether it is correct, or somehow during convert to text it is truncated ? because quite weird that all in the same length.

 

 

Sounds like the length of the datatype you're using ends at 1024.  Look at varchar(max)

Sorry, I copy it to Excel and use Len function, not sure why it is give me that 1024. 

But I add a Calculated Column directly in Power BI in the same table, turns out it gives me what I think the correct length.

 

Some variance of length shows, but not exceed 32677, yet it doesn't show me the image instead a broken image icon.

 

Thinking of giving up as well. 😞

I gave up on this because I need large/high res. images.  An alternative approach was to write the images to temp. files on disk and insert a link to these in my dataset.  Seems like PowerBI only supports small icon type images out of the box.  Ces't la vie.

andy_scott42 seems to have it nailed.   Whether or not you'll be able to use the techniques in this article will depend on how big your images will be (in Pixels) and how detailed

 

In the screenshot below .. the one I will probably mimic for my work is #3.  Using Snagit, I take the image and resize it to something less than or equal to 100x100 pixels.   I then resize the canvas to 105x105 pixes to give a border.  I don't expect to need anything displayed in my visualizations that will be any bigger than that.

 

Explainit.png

 

#1 is small and low resolution.  Its too blurry and quite unappealing given the 4 bit color application

#2 is small and as a result - blurry

#3 will work fine (see description above).  Its 9KB (well under the 32KB mentioned as the limit)

#4 is a big image with only 16 colors.   The reduced color works for the most part, but that may not hold for other images

#5 illustrates what can happen to an image larger than 32kb

 

So -- think about how big your images really need to be for your reports/dashboards.  Format in advance.  If, at your target image size you're still over 32KB, then consider reducing resolution or color

I ran into the same issue!  Cropped photos Smiley Mad Anybody come up with a genius solution to this yet?

syale
Frequent Visitor

Is there a size limit for the png?

There is a limit.  Nothing documented but they have to be "smaller".  I tested with a jpg that was 3MB and it failed to render.

I'm using this method to display images in a table and some of the images are being cropped. Is this because the image size is too big? 

nrsleight
Regular Visitor

Could you do a similar thing with SSAS?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.