Number.BitwiseOr

Updated on

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

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

Syntax

``````Number.BitwiseOr(
number1 as nullable number,
number2 as nullable number,
) as nullable number``````

Description

The `Number.BitwiseOr` function performs a bitwise “OR” operation on two numbers. For each bit position, if at least one of the two numbers has a bit set to 1, the result will have that bit set to 1. If neither number has a bit set to 1 at that position, the result will have a 0 bit there. In essence, it combines the bits of both numbers, setting a bit in the result whenever either input number has that bit set.

Examples

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

Example 1

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

``Number.BitwiseOr( 5, 3 ) // Output: 7``

The answer of this expression is 7. But why?

Convert numbers to binary

The first step to perform the Bitwise OR operation is to convert the input to binary.

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

For the XOR operation:

• If either bit of the two numbers (or both) is a 1, the result for that position is 1.
• If both bits are 0, the result is 0.

Align the binary numbers

Let’s place our numbers side by side and do the “OR” operation:

``````  5:  101
3:  011
-----------
OR:  111
``````

The result of this operation is the binary number `111`, which translates to the decimal number `7`.

Example 2

In another example we compare the numbers 1 and 31.

``Number.BitwiseOr( 1, 31 ) // Output: 31``

Convert numbers to binary

To understand this, let’s first express the input numbers in binary form:

• The number 1 translates to `00001` in binary.
• Meanwhile, 31 is represented as `11111`.

Align the binary numbers

Comparing these binary values side by side:

``````  1:  00001
31:  11111
-----------
OR:  11111``````

The resulting binary number is 11111. When we translate `11111` from binary back into the decimal format, we get the number 31.

Thus, when you input the values 1 and 31 into the `Number.BitwiseOr` function, it returns us the value 31. This is primarily because the number 31 already possesses all its bit positions set to 1. Hence, using the “OR” operation with 1 doesn’t alter its value in any way.

The Bitwise OR operation essentially returns a binary position as 1 if, in that specific position, at least one of the two compared bits is 1.

Practical use-cases

So where is the Number.BitWiseOr function useful?

Storing color values

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 ]``````

Storing permissions

In another scenario, imagine you’re building a system where users can have multiple permissions, and you want an efficient way to store and check these permissions. Let’s define these permissions using individual bits:

1. Read = 1 (binary: 0001)
2. Write = 2 (binary: 0010)
3. Execute = 4 (binary: 0100)
4. Delete = 8 (binary: 1000)

If you want a user to have both “Read” and “Write” permissions, you can use the `Number.BitwiseOr` operation:

``````UserPermission = Read | Write
= 0001 | 0010
= 0011 (which is 3 in decimal)``````

So, by using `Number.BitwiseOr`, the user’s permission is now set to `3`, which represents both “Read” and “Write”.

This system is highly scalable. If you had dozens of permissions, you wouldn’t need dozens of boolean fields or a large array to check/set permissions; you’d just need an integer and appropriate bitwise operations.

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

Other functions related to Number.BitwiseOr are:

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