Number.BitwiseAnd

Number.BitwiseAnd is a Power Query M function that performs a bitwise “And” operation between two given numbers. The function returns the result of the bitwise “And” operation

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

Syntax

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

Description

The Number.BitwiseAnd function conducts a bitwise “AND” operation on two numbers. For each bit position, the result will have a bit set to 1 only if both corresponding bits in the input numbers are set to 1. If either of the bits is 0, the result will have a 0 bit in that position. In essence, it returns a number with bits set where both input numbers have bits set and nowhere else.

Examples

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

Example 1

To find the result of the Bitwise And operation between 10 and 22, we use:

Number.BitwiseAnd( 10, 22 ) // Output: 2

Convert numbers to binary

Before we understand the output, let’s represent our numbers in binary:

• The number 10 in decimal is 1010 in binary.
• The number 22 in decimal is 10110 in binary.

To compare them bit by bit, align the binary representations so they have the same length by adding zeroes to the shorter number:

 10:  01010
 22:  10110

The essence of the bitwise “AND” operation is straightforward:

• When both corresponding bits are 1, the result bit is 1.
• If either bit (or both) is 0, the result bit is 0.

Align the binary numbers

Now, comparing each bit of the two numbers:

 10:  01010
 22:  10110
-----------
AND:  00010

The binary result 00010 translates back to the decimal number 2.

Therefore, when using the Number.BitwiseAnd function with 10 and 22, the outcome is 2. This is because, in their binary form, only the second position from the right has 1s in both numbers.

Example 2

For the numbers -5 and 8, the Number.BitwiseAnd function operation is:

Number.BitwiseAnd( -5, 8 ) // Output: 8

Convert numbers to binary

The number 8 in decimal translates easily to 1000 in binary. Converting -5 is a bit more involved because of its negative sign. We use the “Two’s Complement” method:

1. Start with the binary for 5: 0101.
2. Invert every bit: 1010.
3. Add 1 to the result: 1010 + 1 which equals 1011.

Thus, -5 in binary is represented as 1011.

Align the binary numbers

To match bit by bit, place the binary numbers one above the other:

 -5:  1011
  8:  1000

Bitwise “AND” checks each pair of bits:

• If both bits are 1, the result bit is 1.
• Otherwise, the result bit is 0.

When you compare the bits of -5 and 8:

 -5:  1011
  8:  1000
-----------
AND:  1000

The outcome in binary, 1000, corresponds to the decimal number 8.

In the Number.BitwiseAnd function, when -5 and 8 are input, the result is 8. This is because the only position where both -5 and 8 have a 1 is in the third position from the right, representing the decimal number 8.

Practical use-case

Where is this useful? Imagine a scenario where you are storing color values for pixels. Each color (red, green, blue) can have values ranging from 0 to 255, which can be represented by 8 bits (a byte).

If you’re storing each color in a separate integer, you’d be using a lot of unnecessary space.

However, by using the Number.BitwiseShiftLeft operation together with Number.BitwiseOr, you can efficiently pack all three colors into a single 32-bit integer:

let
    red = 200 ,   // your red value here, ranging from 0 to 255
    green = 150 , // your green value here, ranging from 0 to 255
    blue = 77 ,   // your blue value here, ranging from 0 to 255

    greenShifted = Number.BitwiseShiftLeft(green, 8),
    blueShifted =  Number.BitwiseShiftLeft(blue, 16),

    packedColor = Number.BitwiseOr( red, Number.BitwiseOr( greenShifted, blueShifted) )
in
    packedColor // Returns 5084872

Here, the green value is shifted left by 8 bits and the blue value by 16 bits. The three 8-bit values are then combined into one integer of 5084872 using the OR operation.

To reverse this operation you can make use of the Number.BitWiseShiftRight and Number.BitWiseAnd function as shown here:

let
    packedColor = 5084872,  // This is the packed color value

    redUnpacked =   Number.BitwiseAnd( packedColor, 255),
    greenUnpacked = Number.BitwiseAnd( Number.BitwiseShiftRight( packedColor, 8 ), 255 ),
    blueUnpacked =  Number.BitwiseAnd( Number.BitwiseShiftRight( packedColor, 16 ), 255 )
    Result = 
  [ 
      Red =   redUnpacked, 
      Green = greenUnpacked, 
      Blue =  blueUnpacked
    ]
in
  Result // Returns [ 200, 150, 77 ]

To conclude, in your data transformation work these operations are useful for both data compression and certain mathematical operations.

Related functions

Other functions related to Number.BitwiseAnd are:

• Number.BitwiseNot
• Number.BitwiseOr
• Number.BitwiseShiftLeft
• Number.BitwiseShiftRight
• Number.BitwiseXor

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