# Number.BitwiseAnd

Updated on

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.

Other functions related to Number.BitwiseAnd are:

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