Number.BitwiseShiftRight

Updated on

Number.BitwiseShiftRight is a Power Query M function that performs a bitwise shift to the right on a given number by a specified number of bits. The function returns the result of the bitwise shift to the right operation.

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

Syntax

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

Description

The Number.BitwiseShiftRight function shifts the bits of a given number to the right by a specified number of positions. It discards the rightmost bits and fills the leftmost positions based on the original number’s sign bit. This action effectively divides the original number by 2 raised to the power of the specified positions, rounding down if necessary.

Examples

The Number.BitwiseShiftRight function shifts the bits of its first input to the right, by a number of positions specified by its second input. Considering the example provided:

Number.BitwiseShiftRight( 10, 1 ) // Output: 5

The decimal number 10 is represented in binary as 1010. When you shift 1010 (which is 10 in binary) to the right by 1 position, you remove the rightmost bit and move the remaining bits one position to the right. So, 1010 becomes 101. The binary number 101 translates back to the decimal number 5.

Therefore, when you perform Number.BitwiseShiftRight(10, 1), the bits of 10 are shifted to the right by one position, resulting in the value 5.

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.BitwiseShiftRight are:

BI Gorilla Youtube Channel

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