Binary to Text always gets weird characters

[djpirra]

Hi everyone,

I am currently trying to convert an identified column as binary into its text format.

Basically this column is supposed to have a connectionstring.

The function that I am trying to use is Text.FromBinary but the output gets like this:

»Êo…Aڐ*[™ÄƒkWúq¯/Œ�¿ÜÎ�vÁ!ÒíÕº®tp´rxÑøŠ.ÑDP�–<m²­þ9AMhöD4KÅ�qñjQϘƒ§š8éŸó ¡…ô��ªÕswÐngp¤3hH�u?}​​​​​​​�€Óç䘋���6¦���†f¤¤‚ô

What is missing?

Thank you

Regards,

LS

[amitchandak]

@djpirra , have tried Binary.ToText with encoding option https://docs.microsoft.com/en-us/powerquery-m/binary-totext

[djpirra]

Yes I did, when doing it the output is something like this:

BQMmmJ++VoLV6Aipye2Sn1/A+c43QtQuH38tp/WNmLkT/cfAbcz4NKBHx0TIYRXwE4eo+LjjKL8IgXpUQRNmZy6rkNDz7clsRrFPJSxMPJV+X8CnBGQC8itc8Kx3DZjufhSnC3VdP2tOVXJuXHLqM+R0XFygbmexgwzb7bHZ7ypclBp8h1EjFbZJi8VMLirzeitkqTN/azLl8dV4PZx7vvPKW+9NpDmo

[PhilipTreacy]

Hi @djpirra 

How was the connection string encoded to binary?  If you know that you can decode it.  If you don't we're just guessing how to do it.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[djpirra]

Hi Phil,

I guess the column in the catalog table is a VARBINARY? Its being recognized automatically by Power BI as Binary.

[v-shex-msft]

HI @djpirra,

Any encrypt existed in your file? If this is the case, you can only decode the encrypted string instead of raw file contents. Did you know the raw file format?

Regards,

Xiaoxin Sheng

[djpirra]

Hi Xiaoxin,

What file you refering to?

The data is directly coming from the "Catalog" table of the Reporting Services database.

Thank you

[v-shex-msft]

Hi @djpirra,

How about enabling the 'advance option' - 'SQL statement' to direct use t-SQL query to convert them to string on the database side? It should have better compatibly than processing on the power bi side.

Regards,

Xiaoxin Sheng

[PhilipTreacy]

Hi @djpirra 

The data could be compressed, or you might not be decoding it with the correct algorithm. Do you know how it was converted from text into binary?

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[djpirra]

Hi Phil,

The data comes from a Reporting Services catalog table where this column is a varbinary.

[PhilipTreacy]

Hi @djpirra 

Can you share one of these binary values so I can try converting it?

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[djpirra]

@PhilipTreacy  when I click on the binary link for one of the rows I get a file icon.

When I click the file icon and select show text it breaks into two rows having these values:

M«r,s¤>¿|3ÅiA=Kwxj�¥Ù�3B��ªë�„€³8×Ïña¹\Oƒ¸f½2Õ…Q�£R=J

þˆc.ÀÒLh�Õnä‡l�ˆÜ‡�=û¯\®Z“O�Ý°á²kqÍ×�ê´õª2Qà¡­Üi[Z9CŸÃýío¯�o—]Ò�â¸?ó\¶�SµF°÷Ók†Š�ú¦*+&aÞ±Sûw’4

Thanks

[vanessafvg]

that looks corrutped to me... are you able to share some data?