Image Binary Data to Image

[Anonymous]

Hello,

I have a table in SQL Server which has Image binary data in the below format - 

0x1F8C0200B28C010064
0x1F8C0200DE720100CC
0x1F8C0200DE720100CC
0x1F8C0200B28C010064
0x1F8C0200B28C010064
0x1F8C0200B28C010064

This is just part of the hex string. I am converting this into image URL using below function - 

let
    BinaryToPbiImage = (BinaryContent as binary) as text=>
let
    Base64 = "data:image/jpeg;base64," & Binary.ToText(BinaryContent, BinaryEncoding.Base64)
in
    Base64 
in
    BinaryToPbiImage

Now whats happening is the image is not being displayed. Is there some change that I need to do in my code.

Any suggestion would be helpful.

[MmmVvv_23]

I didnt notice any special difference between:

let 
    BinaryToPbiImage = (BinaryContent as binary) as text=>
let
    Base64 = "data:image/jpeg;base64, " & Binary.ToText(BinaryContent,BinaryEncoding.Base64)
in
    Base64
in
    BinaryToPbiImage

and:

"data:image/jpeg;base64,"&[Content]

In my table or SimpleImage viz looks all the same.

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if the method in this blog could help you.

---

Step 7

Change the data type of ImageData (Binary) column to text from the top panel's Transform tab.

Step 8

Go to "Add Column" and click on "Custom Column". It will open a window. Enter the column name and formula given below and click OK.

“data&colon;image/<image_format>;base64,”&[ImageData<binary_column_name>]

Step 9

On Data tab, select Image column and change its data category to Image URL.

---

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@AlexisOlson There is something I noticed, when I am using the function and encoding into base64 I am getting something like this
H4wCAMU6AADNuWVQHEDXJjpA8ABJCO7ungGCBncL7u7ubiFY

Whereas if I use the same image and convert the image itself to base 64  the value is - 

iVBORw0KGgoAAAANSUhEUgAAAV0AAAEOCAIAAACl8FO5AA

[Anonymous]

Yes I am using colon. When I am copying the code here its automatically converting into it "&colon;"

[AlexisOlson]

Please double-check that you are actually using a colon ":" rather than "&colon;" in your function.

[Anonymous]

I am not sure. The original image is either in jpeg or png

[Anonymous]

Yes, I do have it in binary. SQL Data column type is Image if that helps

[AlexisOlson]

Hmm. Is the binary a JPEG encoding? I'm just shooting in the dark.

[Anonymous]

@AlexisOlson I am referring to the same article. But its not converting.

Yes, I have set it as an Image URL. Also, does the source field needs to be binary format or text? This is what I see.

[AlexisOlson]

The data source should be binary, not text.

[AlexisOlson]

Did you set the Data Category as Image URL as explained here?

https://blog.gbrueckl.at/2018/01/storing-images-powerbi-analysis-services-data-models/