Convert Binary data

[AltusTellus]

Hi experts,

I have an issue with converting binary data to a readable PDF file. What I have done until now:

In a MS SQL table with financial transaction lines, I have a column with the binary data of the document which is attached to the row with the financial transaction. The lenght of the binary data is about 34.000 positions.

In PBI Desktop the data has a maximum of 1024 positions in the column [Document]. I have added a new column with the formula:

Binary.ToText([Document], BinaryEncoding.Base64)

In this new column (named: [Document_Def]) I only have errors. By clicking next to [Error] I see the details:

Exression.Error: Cannot convet the value "JVBERi0xLjQKJeLjz9MN..." to Binary type. After that followed bij the complete text of this field and ends up with Type=[Type].

Is there a solution for this? At the end I want the data to be shown as a PDF-document by PDF Viewer.

Many thanks foor answers / suggestions!

Grtz, Alfred

[v-kkf-msft]

Hi @AltusTellus ,

Please check if the type of [document] column is Binary. 

Or you can try the following formula to see if it works.

Binary.ToText(Binary.From([Document]), BinaryEncoding.Base64)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[AltusTellus]

After the import into PBI Desktop, indeed, the [Document] column is of the type binary. I also used your formula, unfortunately with the same issue. Here you can find the orginal data and the data in PBI after the import. 

Document example SQL to PBI 

You will see that the total lenght of the original document is approximately 34.000 characters. In PBI column it is 1.024 characters after your formula. 

Looking forward to your reply, many thnx!

[v-kkf-msft]

Hi @AltusTellus ,

I can't reproduce your problem, could you allow editing the shared excel? That way I can test it based on your data. Currently I only can read the excel and cannot copy the data.

Best Regards,
Winniz

[AltusTellus]

I have checked, but when you wait about 30 seconds, the file can be downloaded. 

[AltusTellus]

@v-kkf-msft did you have the time to takea look at the file with the example? 🙂

[v-kkf-msft]

Hi @AltusTellus ,

Sorry for the late reply. I used your sample data, then created the following custom column and no errors were generated. 

= Binary.ToText(Text.ToBinary([Original SQL Document]), BinaryEncoding.Base64)

Is your example PDF blank? If not, please let me know and I will test again.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[sevenhills]

Check this article and see if it helps 

https://customertell.com/displaying-pdf-files-stored-in-sql-server-in-power-bi/

[AltusTellus]

Hi @sevenhills I have checked this document, unfortunately not the solution. After import in PBI the column [Document] gives me "Binary" and when I add a column to convert the column [Document] it gives me the binary data. What is going wrong, any idea(s)?

[sevenhills]

Did you try enable tracing on your PBI Desktop and see the log , to get to know more details?

Trace, logs: https://blog.crossjoin.co.uk/2016/06/09/power-bi-diagnostics-trace-logs-and-query-execution-times-ag...