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.

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

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