This post is also available in: German

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:

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!

8 thoughts on “Encrypting Values in Power BI

      • 21. November 2019 at 15:51
        Permalink

        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

        Reply
  • 10. April 2019 at 15:31
    Permalink

    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?

    Reply
    • 19. December 2019 at 20:03
      Permalink

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

      Reply
  • 7. July 2020 at 11:51
    Permalink

    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. ๐Ÿ™‚

    Reply
    • 30. September 2020 at 10:12
      Permalink

      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.

      Reply

Leave a Reply

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

%d bloggers like this: