Forum Discussion

mjfulke's avatar
mjfulke
Microsoft Employee
8 years ago
Solved

How to use Images Stored in a SQL Server Table with Power BI

 Power BI has several options to display images in a report or dashboard but they mostly require that the image is a url to a publicly accessible website.    What I'll show you in this post is a way ...
Tips and Tricks
mjbernier's avatar
mjbernier
Helper II
7 years ago
Tried all the steps laid out here, but it didn't work. All I see are either the URLs or the "broken image" symbols. Figuring it might be an image size issue, I reduced the images to less than 100K and still no joy. I checked the Ideas forum and this one was first introduced over THREE YEARS ago with no response from Microsoft. Why do we have to take in-house images sitting in tables and export them to a file server, then link to them in order to display them in an in-house report? It's beyond ridiculous.
andy_scott42's avatar
andy_scott42
Helper II
7 years ago

I've found that the maximum length a string can have to be processed by Power BI is 32766 characters.

So once you've converted your image to a Base64 string, if the length of the string is greater than this then it will crop the image.

 

  • lunds007's avatar
    lunds007
    New Member
    7 years ago

    I ran into the same issue!  Cropped photos :smileymad: Anybody come up with a genius solution to this yet?

  • DNickell's avatar
    DNickell
    New Member
    7 years ago

    andy_scott42 seems to have it nailed.   Whether or not you'll be able to use the techniques in this article will depend on how big your images will be (in Pixels) and how detailed

     

    In the screenshot below .. the one I will probably mimic for my work is #3.  Using Snagit, I take the image and resize it to something less than or equal to 100x100 pixels.   I then resize the canvas to 105x105 pixes to give a border.  I don't expect to need anything displayed in my visualizations that will be any bigger than that.

     

     

    #1 is small and low resolution.  Its too blurry and quite unappealing given the 4 bit color application

    #2 is small and as a result - blurry

    #3 will work fine (see description above).  Its 9KB (well under the 32KB mentioned as the limit)

    #4 is a big image with only 16 colors.   The reduced color works for the most part, but that may not hold for other images

    #5 illustrates what can happen to an image larger than 32kb

     

    So -- think about how big your images really need to be for your reports/dashboards.  Format in advance.  If, at your target image size you're still over 32KB, then consider reducing resolution or color

  • admin_xlsior's avatar
    admin_xlsior
    Post Prodigy
    7 years ago

     Hi, I tried same step, and count the character is 1024, but still it is display as broken image. My image is png.

     

    However by look at the character length, I'm not sure whether it is correct, or somehow during convert to text it is truncated ? because quite weird that all in the same length.

     

     

    • cymorg's avatar
      cymorg
      Resolver I
      7 years ago

      I gave up on this because I need large/high res. images.  An alternative approach was to write the images to temp. files on disk and insert a link to these in my dataset.  Seems like PowerBI only supports small icon type images out of the box.  Ces't la vie.

    • mjfulke's avatar
      mjfulke
      Microsoft Employee
      7 years ago

      Sounds like the length of the datatype you're using ends at 1024.  Look at varchar(max)

      • admin_xlsior's avatar
        admin_xlsior
        Post Prodigy
        7 years ago

        Sorry, I copy it to Excel and use Len function, not sure why it is give me that 1024. 

        But I add a Calculated Column directly in Power BI in the same table, turns out it gives me what I think the correct length.

         

        Some variance of length shows, but not exceed 32677, yet it doesn't show me the image instead a broken image icon.

         

        Thinking of giving up as well. :(