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
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

5 REPLIES 5
Element115
Impactful Individual
Impactful Individual

@Lyssillic  Fantastic solution!  Thank you so much for posting it. I just ran into this issue and could not find anything anywhere, not even in the MS docs. Although as of 2022-02-11, a binary column can be converted to text in the Power Query editor, but even so, it still triggers the query not compatible with DQ error. Even after I did the  exact same concat you did, but I did mine inside Power Query using M. And PQ still didn't want to accept it.  Looks like there is a limitation on the max size of an incoming stream of binary data.  But if it is text, then it is fine.  Very strange.

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

 

 

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.

@v-shex-msft I need to add one more thing: the OP's solution gets rid of the error message in PQ, but in Power BI, the visual stays stuck in load-forever-mode and then after a long long time, fails with a can't-load-visual style error message.  So the problem is only half solved at this point.

 

Any idea how load images from an on-prem SQL Server via DirectQuery?

 

And before anybody asks:  I tried everything:  with the 'data:image/jpeg;base64' concatenated at the very beginning of the byte stream, without it, with and without Image URL categorization, and so on.  The visual still refuses to load the image even though now all code runs without error message.  And like I said before, if I concat the base64 info inside Power Query instead of at the source, the M query fails miserably.  

@v-shex-msft   I just ran into this issue and could not find anything anywhere, not even in the MS docs. Although as of 2022-02-11, a binary column can be converted to text in the Power Query editor, but even so, it still triggers the query not compatible with DQ error. Even after I did the  exact same concat the OP did, but I did mine inside Power Query using M. And PQ still didn't want to accept it. 

 

Is there is a limitation on the max size of an incoming stream of binary data? If yes, what is it AND where is it documented?  I couldn't find anything online regarding this except the thread here.

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.