Number.BitwiseNot

Number.BitwiseNot is a Power Query M function that performs a bitwise “Not” operation on a given number. The function returns the result of the bitwise “Not” operation.

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

Syntax

Number.BitwiseNot( number as any ) as any

Description

The Number.BitwiseNot function performs a bitwise “NOT” operation on a number. For each bit in the input number, if it’s a 1, the result will have a corresponding bit of 0, and vice versa. Essentially, it inverts each individual bit of the number. For example, applying Number.BitwiseNot to the binary representation 1010 would yield the result 0101.

Examples

Let’s see how the Number.BitwiseNot function works with some examples.

Example 1

For the number 5, the Number.BitwiseNot function is:

Number.BitwiseNot( 5 ) // Output: -6

Convert numbers to binary

The number 5 in decimal corresponds to 101 in binary. In computers, we usually work with standard bit lengths, like 32 or 64 bits. For simplicity, let’s use an 8-bit representation. So, in 8 bits, 5 becomes 00000101.

Align the binary numbers

To get the BitwiseNot, invert each bit (1s become 0s, and 0s become 1s):

  5:  00000101
NOT:  11111010

The resulting binary number is 11111010.

The leftmost bit in binary numbers (in our 8-bit example, the far left 1) often determines the sign of the number. A leading 0 indicates a positive number, while a leading 1 denotes a negative. This system is the “Two’s Complement” method.

To find the decimal value of our negative binary number:

1. Invert the bits of 11111010 to get 00000101, which is 5 in decimal.
2. Add 1 to the result: 5 + 1 = 6.

Given the leading bit is 1 (indicating negative), the result is -6.

Example 2

Here’s another example.

Number.BitwiseOr( -12 ) // Output: 11

Convert numbers to binary

First, get the binary representation of the positive number 12. Assuming an 8-bit system for simplicity, 12 is 00001100.

To find the Two’s Complement (which gives us -12):

• Invert all the bits: 11110011
• Add 1 to the result: 11110011 + 1 = 11110100

So, the number -12 is represented as 11110100 in an 8-bit binary system.

Align the binary numbers

When we apply the Number.BitwiseNot operation to -12, we’re flipping all the bits.

-12:  11110100
NOT:  00001011

The resulting binary number is 00001011.The binary number 00001011 translates to the decimal number 11.

And that’s why Number.BitwiseNot(-12) gives you the number 11. It’s all about flipping bits and understanding how negative numbers are represented in the binary world!

Practical use-case

As the above example showed, the Number.BitWiseNot function can be used for performing the Two’s complement method. . Two’s complement is the standard way to represent signed integers in binary. It encodes positive numbers normally, but represents negative numbers as the bitwise NOT of their absolute value plus one. This method streamlines arithmetic operations, allowing addition and subtraction to utilize the same hardware, and ensures consistent representation of positive and negative zeros.

To compute the two’s complement:

1. Convert the number to its binary representation.
2. Invert all bits using Number.BitwiseNot.
3. Add 1.

In Power Query you can perform such by writing:

let
    originalNumber = 5,
    invertedNumber = Number.BitwiseNot( originalNumber ),
    twosComplement = invertedNumber + 1
in
    twosComplement // Returns - 5

Using this approach gives the two’s complement representation of the number.

Related functions

Other functions related to Number.BitwiseNot are:

• Number.BitwiseAnd
• Number.BitwiseOr
• Number.BitwiseShiftLeft
• Number.BitwiseShiftRight
• Number.BitwiseXor

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/number-bitwisenot