Forum Discussion

Lyssillic's avatar
Lyssillic
Icon for Helper I rankHelper I
6 years ago
Solved

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 ma...
Need Help
  • Lyssillic's avatar
    Lyssillic
    6 years ago

    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

     

     

Element115's avatar
Element115
Icon for Memorable Member rankMemorable Member
4 years ago

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.