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
Number.BitwiseXor( number1 as nullable number, number2 as nullable number, ) as nullable number
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.
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?
5in binary is
3in binary is
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
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.
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
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: