Number.BitwiseShiftLeft

Updated on

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

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

Syntax

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

Description

The Number.BitwiseShiftLeft function shifts the bits of a given number to the left by a specified number of positions. It adds zeros to the rightmost positions of the shifted number. This action multiplies the original number by 2 raised to the power of the specified positions. For example, Number.BitwiseShiftLeft(5, 8) shifts the bits of 5 left by 8 positions, resulting in 1280.

Examples

So what does the Number.BitWiseShiftLeft function do?

Basic operation

Let’s look at an example. The function takes two numbers. It shifts the bits of the first number to the left by the number of positions specified by the second number.

Number.BitwiseShiftLeft( 5, 8 ) // Output: 1280

The number 5 is depicted in binary as 101.

In the context of our example, we’re shifting this binary representation of 5 (which is 101) to the left by 8 positions. Performing this operation gives us the binary sequence: 10100000000.

This newly obtained binary string, 10100000000 is equivalent to 1280 in decimal.

Therefore, when you use Number.BitwiseShiftLeft on the inputs 5 and 8, the function shifts the bits of 5 to the left by 8 places. This bit manipulation transforms the initial number 5 into its new value, 1280.

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

BI Gorilla Blog

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