Decrypting a SQL Server Column from Binary encryption during PBI Data Import

[RustyNails]

Hello. I have a SQL Server table with an encrypted Column that uses CEK_Auto1 Encryption key (deterministic). It converted the column into a Hex format (0x0126E595C47C89086...). In PowerBI I need to decrypt this column (my user should have been given the SQL privileges to do so). The Column's original datatype is nvarchar(100) but in reality it is just numbers being stored in nvarchar type.

In PBI the column shows up as "Binary". How do I Decrypt this column in PBI?

Thanks

[dax · Accepted solution]

Hi RustyNails, 

According to yiur description, it seems that you want to convert binary to hex or text, right? If so, you could try below M code to see whether it work or not.

= Table.AddColumn(Source, "Custom", each Binary.ToText([a],BinaryEncoding.Base64))
or
= Table.AddColumn(#"Added Custom", "Custom.1", each Binary.ToText([a],BinaryEncoding.Hex))

 You could refer to Binary.ToText ,Converting Binary to hex value or numeric value  or this blog  for details.

Best Regards,
Zoe Zhi

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