This post is also available in:

Hits: 15863

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.

If you want to read more about Power BI, subscribe to our newsletter.

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:

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”.

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.

Again, we have received a binary, but now compressed, value. This value is converted back to text with “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.

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

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

And we already have the desired result.

And here are the individual 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.

Encryption of entire rows

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

The function called “fn_Hashing” can then be used by combining the values of the whole 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!