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

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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

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