This post is also available in: de

Hits: 239

Encrypting values – do you have data in your datasets that you need to encrypt? Here I show you how encryption can be implemented in Power BI using Power Query functions.

Use Cases

Typical use cases for encryption include credit card numbers, social security numbers and account numbers.

These may be required for evaluations but should not be visible in plain text. A complete or partial encryption is recommended here.

Encrypting Values in Power BI

Power BI or Power Query knows no function which can encrypt values “out of the box”. But by cleverly combining binary functions, the goal can still be achieved.

The first step is to convert the value to be encrypted into a binary value, then compress it, convert it into a list, and finally convert it back into a text. Sounds more complicated than it is.

Let’s look at this dummy credit card number:

5105 1051 0510 5100

We want to encrypt these in such a way that a report user cannot draw any conclusions about the number. The result will look like this:
1f8b08000000000004000b48cb2c2e492d0200be8fc57807000000

Stepy by step

This small table serves as a starting point:

starting point
starting point

First, the “CreditCard Number” field must be converted to a binary value on a 64-bit basis. We achieve this with the first function “Text.ToBinary” and the parameter “BinaryEncoding.Base64”.

Text.ToBinary
Text.ToBinary

The result is a binary file, which we now convert to GZip format with the function “Binary.Compress”, which is placed before the above function.

Binary.Compress
Binary.Compress

Again, we have received a binary, but now compressed, value. This value is converted back to text with “Binary.ToText”.

Binary.ToText
Binary.ToText

The result looks as follows, but does not yet correspond to the desired value:

H4sIAAAAAAAEADM1NDBVAGJDBSA2UABiAwD0UmZfEwAAAA==

What is missing is the conversion of the value after compression into a list. So, we delete the last step and use “Binary.ToList” instead.

Binary.ToList
Binary.ToList

We put the list together again with “Binary.FromList”.

Binary.FromList
Binary.FromList

Finally, we use “Binary.ToText”, this time with the parameter “BinaryEncoding.Hex”.

Binary.ToText
Binary.ToText

And we already have the desired result.

Result
Result

And here are the individual values:

Encrypted values
Encrypted values

As you can see, all values in the column “CreditCard Number” are encrypted.

Partial encryption

It is also possible to encrypt only parts of a value with the usual text functions. In this example only the last eight digits are encrypted, the remaining digits still appear in plain text.

Code partial encryption
Code partial encryption

 

Code partial encryption
Code partial encryption

Encryption of entire rows

Entire rows can also be encrypted. To do this, the statement is converted into a user-defined function.

encryption function
encryption function

The function called “fn_Hashing” can then be used by combining the values of the whole row.

Code encrypted row
Code encrypted row
encrypted row
encrypted row

The file with the sample code can be found here.

Conclusion

Encrypting values in Power BI is easy with a little knowledge of Power Query and the “M” language. What other solutions do you know to encrypt values in Power BI? Let us share your experiences in the commentary.

You can read more about Power BI here.

Happy Querying!

2 thoughts on “Encrypting Values in Power BI

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: