This post is also available in:

Hits: 9945

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.

Inhaltsverzeichnis / Table of Contents

**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!

Hi! Great article! Thanks for the demo pbix. I have tried to do something similar recently. T-SQL also has functions to convert to binary and back to text.

Thank you very much for your kind comment. I’m glad that the blog post helps

Hi Hans

Do similar values get encrypted to the same value using your method?

e.g. 123 becomes XYZ in row 3 as well as row 7 if it appears in both

Hi Thomas – You are absolutely right!

I believe this is not an encryption using a key or secret. It is a conversion, and anybody can convert it back by knowing the encryption steps rather than needing a key, can’t they?

Hi there

You’re absolutely right. In fact, if the user has access to the Power BI Desktop file, the procedure can be reversed.

Hey, This is a really great article! I was looking for this piece for one of my dashboards!

Is there any way we can encrypt the columns without the user being able to decrypt it back? Actually I need to share the pbix file itself with the user(because his ask is like that, he wants to be able to enter his own data source whenever needed 🙂 ). And for that, we want to hide the calculated columns, measures, all DAX codes from the user. So even if he has the pbix file, he should be only able to enter his data and view the visuals, and really not view the DAX codes. 🙂

Hi Ayushi – unfortunately, there is no way to do that. If a user has the pbix, he could see the original values. You have to solve this in the source.