Number.BitwiseXor

Number.BitwiseXor is a Power Query M function that performs a bitwise “XOR” (Exclusive-OR) operation between two given numbers. The function returns the result of the bitwise “XOR” operation.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Number.BitwiseXor(
   number1 as nullable number,
   number2 as nullable number,
) as nullable number

Description

The Number.BitwiseXor function does a bitwise “XOR” operation on two numbers. It gives a result where each bit is set to 1 only if one of the two input bits is 1, but not both. In simple terms, it checks each bit of the numbers and sets the result bit to 1 if they are different.

So where would you use Number.BitwiseXor? You could use the function as a straightforward method for data obfuscation, allowing data to be masked by toggling its bits in a predictable pattern. When applied twice with the same pattern, the XOR operation reverses the obfuscation, ensuring data remains accessible yet temporarily concealed. You can see a practical use-case of this at the bottom of this article.

Examples

Say we want to see the result of the bitwise XOR operation between 5 and 3 using this function.

Number.BitwiseXor( 5, 3 ) // Output: 6

The answer of this expression is 6. But why?

  • 5 in binary is 101.
  • 3 in binary is 011.

For the XOR operation:

  • Compare each bit of the two numbers.
  • If bits differ (one is 1, the other is 0), the result is 1.
  • If bits are the same, the result is 0.

Now, let’s apply this to our numbers:

  5:  101
  3:  011
-----------
XOR:  110

The result of this operation is the binary number 110, which translates to the decimal number 6.

This process showcases the essence of the XOR operation—returning a 1 for positions where the bits of the two numbers differ and a 0 where they are the same.

Practical use-case

Imagine you want to obscure a numeric ID for temporary storage or for obscuring logs. You can XOR it with a key, and then XOR the result again to retrieve the original value.

let
    originalID = 12345, // Some ID you want to obscure
    key = 54321,        // The key to XOR with. Keep this secret!

    // Obscuring the original ID
    obscuredID = Number.BitwiseXor( originalID, key ),  // Returns 58376

    // Retrieving the original ID from the obscured ID
    retrievedID = Number.BitwiseXor( obscuredID, key ), // Returns 12345
    result = [ OriginalID = originalID, ObscuredID = obscuredID, RetrievedID = retrievedID ]
in
    result // Returns [ OriginalID = 12345, ObscuredID = 58376, RetrievedID = 12345 ]

When you run the above Power Query, ObscuredID will be a value different from OriginalID, making it obscured. However, when you XOR ObscuredID again with the same key (54321 in this example), you get back the OriginalID.

Remember, while this can hide data from casual observation, XOR is not encryption, and anyone aware of the key can easily retrieve the original data. For any serious security requirement, always opt for tried and tested encryption methods.

Other functions related to Number.BitwiseXor are:

BI Gorilla Youtube Channel

Last update: August 17, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/number-bitwisexor
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.