cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lyssillic
Helper I
Helper I

Display images from SQL Server database DirectQuery model

I have a SQL Server database with a table that holds pictures in Binary format that I would like to show in a table in a report. I can only use the DirectQuery model, and I have not found a way to make this happen, because I can't change the data type from Binary to Text since it is not supported in the DirectQuery model.

 

Is there a sufficient way to write a SQL Server query or different way to change the Binary to Text to display in the report?

1 ACCEPTED SOLUTION
Lyssillic
Helper I
Helper I

I was able to solve it with a SQL Server query by casting the image to VARBINARY(MAX), and then casting it to XML, and changing the value to VARCHAR(MAX). After that, I concatenated it with 'data:image/jpeg;base64, ', and was able to change the Data Category to ImageUrl. 

 

Here is the query:

 

SELECT
    CONCAT('data:image/jpeg;base64, ', CAST('' as XML).value('xs:base64Binary(sql:column("BinaryPicture"))', 'VARCHAR(MAX)')) AS Picture
FROM (
    SELECT
        CAST(lmePicture AS VARBINARY(MAX)) AS BinaryPicture
    FROM
        Employees
) AS A

 

 

View solution in original post

2 REPLIES 2
Lyssillic
Helper I
Helper I

I was able to solve it with a SQL Server query by casting the image to VARBINARY(MAX), and then casting it to XML, and changing the value to VARCHAR(MAX). After that, I concatenated it with 'data:image/jpeg;base64, ', and was able to change the Data Category to ImageUrl. 

 

Here is the query:

 

SELECT
    CONCAT('data:image/jpeg;base64, ', CAST('' as XML).value('xs:base64Binary(sql:column("BinaryPicture"))', 'VARCHAR(MAX)')) AS Picture
FROM (
    SELECT
        CAST(lmePicture AS VARBINARY(MAX)) AS BinaryPicture
    FROM
        Employees
) AS A

 

 

View solution in original post

v-shex-msft
Community Support
Community Support

Hi @Lyssillic,

I'd like to suggest you check the below blog to know more about how to analyze binary format image files.
Since you are in direct query mode, I'd like to suggest you write a t-sql query to add a custom column to convert binary to base64 text and add the prefix so that power bi can recognized these strings as image.

Storing Images in a PowerBI/Analysis Services Data Models 

Use the BINARY BASE64 Option 

Transact-SQL: Convert VARBINARY to Base64 String and vice versa 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors