Author: James Mounsey-Moran

Data Encryption – Last month my wife and I picked up a detective box called Woodlock University, in there is a puzzle to decrypt some text using a Vigenère Square. (play it if you haven’t its great)

My thought, I can build that and use it for data encryption for PowerBI and share with others working in data.

I found it useful compared to some other solutions as it isn’t just random but a more complex cipher. This means if we still need to create relationships between two tables or more with this text as the identifier it will still link up as expected but act as a secure cipher.

So here it is!

The Vigenère cipher is a method of encrypting alphabetic text using substitution. It works by using a cipher (a keyword or passphrase) to shift the letters of the plaintext. In my example I used the cipher as Aether, but better to have a longer cipher if possible and of course not something that can be guessed.

Note: Whilst this is a quick and relatively easy cipher to implement. AES encryption is definitely what you should be using !

Data Encryption Process

If you write the Plaintext (the word to encrypt) and Cipher (the keyword) beneath each other, repeating the cipher as necessary.

Example:

Plaintext:
“Hello World”
Cipher:
“AETHE RAETH”


Use a Vigenère Square: The Vigenère square is a table where each row is a shifted alphabet (A-Z). The cipher letter determines which row to use, and the plaintext letter determines which column to use for encryption.

Power BI Setup

I’ve set the data encryption as a PowerBI function so it can be called from elsewhere. The cipher is then driven from a parameter called “cipher”

1. Create a new parameter called Cipher – Choose any value you want to use as your cipher
2. Create a new blank query and copy in the below
3. Call the function linking to text you want to encrypt

Also available at the Aether repo here:

https://github.com/AetherAdv/powerbi_data_vigeneresquare

//JamesMM Aether

let
    CipherVig = (CodeWord as text) as text =>
    let
        CipherKey = cipher, // Define the cipher key using a parameter

        // Define the alphabet as a list of uppercase letters A to Z.
        Alphabet = List.Transform({0..25}, each Character.FromNumber(Character.ToNumber("A") + _)),

        // Generate the Vigenère Square (as a list of records).
        // Each record represents a row of the Vigenère square.
        // Record field names are the letters A to Z.
        VigenereSquare = List.Transform(
            {0..25}, // Iterate through the rows of the square.
            each Record.FromList(
                List.Transform(
                    {0..25}, // Iterate through the columns of the square.
                    (offset) => Alphabet{Number.Mod(offset + _, 26)} // Calculate the shifted letter using mod.
                ),
                Alphabet // Use the alphabet as field names for the record.
            )
        ),

        // Clean the input CodeWord by removing spaces and converting to uppercase.
        CleanWord = Text.Upper(Text.Remove(CodeWord, {" "})),
        // Clean the CipherKey by removing spaces and converting to uppercase.
        CleanCipher = Text.Upper(Text.Remove(CipherKey, {" "})),

        // Calculate the length of the cleaned CodeWord.
        WordLength = Text.Length(CleanWord),

        // Repeat the cipher key to match or exceed the CodeWord length.
        // This ensures the key is long enough to encrypt the entire message.
        // Example: HELLOWORLD becomes CIPHERCIPHE
        RepeatedCipher = Text.Start(Text.Repeat(CleanCipher, Number.RoundUp(WordLength / Text.Length(CleanCipher))), WordLength),

        // Encrypt each character of the CodeWord.
        EncryptedList = List.Transform(
            {0..WordLength - 1}, // Iterate through each character of the CodeWord.
            (index) =>
                let
                    // Get the current character from the CodeWord.
                    Char = Text.At(CleanWord, index),
                    // Get the corresponding character from the repeated cipher key.
                    KeyChar = Text.At(RepeatedCipher, index),
                    // Find the index of the CodeWord character in the alphabet.
                    CharIndex = List.PositionOf(Alphabet, Char),
                    // Find the index of the cipher key character in the alphabet.
                    KeyIndex = List.PositionOf(Alphabet, KeyChar),
                    // Perform the Vigenère encryption.
                    EncryptedChar =
                        if CharIndex = -1 then Char // If the CodeWord character is not in the alphabet, keep it as is.
                        else if KeyIndex = -1 then Char // If the cipher key character is not in the alphabet, keep the CodeWord character as is.
                        else try Text.From(Record.Field(VigenereSquare{KeyIndex}, Alphabet{CharIndex})) otherwise Char // Retrieve the encrypted character from the Vigenère square. If an error occurs, keep the original character.
                in
                    EncryptedChar // Return the encrypted character.
        ),

        // Combine the list of encrypted characters into a single text string.
        EncryptedWord = Text.Combine(EncryptedList)

    in
        EncryptedWord // Return the encrypted word.
in
    CipherVig