Number.BitwiseOr

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.

Related functions

Other functions related to Number.BitwiseOr are:

• Number.BitwiseAnd
• Number.BitwiseNot
• Number.BitwiseShiftLeft
• Number.BitwiseShiftRight
• Number.BitwiseXor

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