Decrypt string values (AES256) from a column of a table in a query

[J_Mug]

HI,

I want to decrypt string values from a crypted column on a table imported as a query in my Power BI report. The string is crypted in AES256.

How can I do in Power Query to decrypt in a new column the content of this crypted column for each row of my query ? (dataset).

Thank in advance,

[Nadjia]

Hello, 
i am currently facing the same issue and i was wondering how have you delt with it last time to decrypt your query ?

Thanks in advance,

[AlexisOlson]

Where do you get the secret encryption key from in your scenario?

[J_Mug]

We receive it from our IT team, the string that contains the information is embedded in QR codes in tickets from third-party POS terminals in our stores, where it travels to a survey page where the client gives his response and we record the metadata from the transaction, it stays encrypted because the survey software can't decrypt.

This AES256 encryption is necessary because this data will be public in the ticket QR code and we don't want that metadata to be easily obtained by third parties.

We also thought about having a key in the survey and then connecting to the transactional system, but it was encrypted too, and we would need to decrypt anyway.

Therefore we need to decrypt after the metadata has passed through the survey software to have more information on that transaction.

I hope this helps.

[AlexisOlson]

I don't think there's a native implementation for AES256 in Power Query.

This leaves a few options:

1. Decrypt before loading into Power BI (e.g. with SQL).
   • This is probably the simplest approach.
2. Use a Python or R script within Power Query.
   • Both of these languages should have packages/libraries for AES.
3. Write your own decryption implementation in M (or hire someone else to).
   • Possible but not really advisable. I've done this sort of thing with SHA256 but wouldn't feel comfortable using my own implementation for production purposes.

[Nadjia]

Hello how can we use Python since Power bi service do not support the Crypto library as for R  digest library isn't working correctly for my case