cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Add columns based on Bitwise operation

Hi!

I need to separate every bit in a byte and create a column for every bit that will state "Activated" or "the name of the bit" when a bit is activated.

The solution that I'm doing now is partially working and I can't figure out why it is not fully working.

I have created 8 different custom function, one for each bit. It takes the column with one byte as parameter and it performs two bitwise operations, the first shifts to the right the bit that is in the position I want to evaluate, and the second one adds the number 1 to that bit, if the result of this sum is 1 then the bit is activated and I'm calling it "BIT + the position of the bit" (BIT 3 is the bit in the 3rd position from right to left).

Rigth, it worked fine with bit 1 and 2, but when I got to bit 3 it is not working correctly. So this is the costum function:

Function for bit 1:

``(Byte0 as number) => if Number.BitwiseAnd(Number.BitwiseShiftRight(Byte0 as number,1),1) = 1 then "BIT 1" else ""``

Function for bit 3:

``(Byte0 as number) => if Number.BitwiseAnd(Number.BitwiseShiftRight(Byte0 as number,3),1) = 1 then "BIT 3" else ""``

and this is a sample of the numbers:

 Binary number What I see as a result now What do I want to see 10000010 BIT 1, BIT3 BIT 1 10001000 BIT3 BIT3

In the first example, only BIT 1 and BIT 7 are activated, therefore that is the result I'm expecting, but somehow it's giving me BIT 3 as well as it was activated. Note that I stopped on BIT 3, I did go further using DAX though and got the same errors / mistakes...in some cases it was giving the correct results in order or not.

Anyone has any idea why this is happening?

1 ACCEPTED SOLUTION
Super User

It's not working because the 10000010 is being interpreted not as a binary number but as a decimal number by PQ. Try shifting 10000010 to the right and see the result. It won't be 1000001

You can store the 10000010 as text and then access the individual figures with

Text.ToList([BinaryNumber]){0}  for the first figure (from the left)

Text.ToList([BinaryNumber]){1}  for the second figure (from the left)

etc

then check whether that figure is a "1" (note you have to check for the character 1, not  number

1, hence the "").

So for bit 1 you would have

``(Byte0 as text) => if Text.ToList( Byte0 ){6} = "1" then "BIT 1" else ""``

for bit 3

``(Byte0 as text) => if Text.ToList( Byte0 ){4} = "1" then "BIT 3" else ""``

etc

Note the number for accessing the different positions in the string is inverted vs what you were using

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

2 REPLIES 2
Helper I

@AlB  right! that makes sense... thanks a lot, it does seems like this work around solution solved the problem.

Super User

It's not working because the 10000010 is being interpreted not as a binary number but as a decimal number by PQ. Try shifting 10000010 to the right and see the result. It won't be 1000001

You can store the 10000010 as text and then access the individual figures with

Text.ToList([BinaryNumber]){0}  for the first figure (from the left)

Text.ToList([BinaryNumber]){1}  for the second figure (from the left)

etc

then check whether that figure is a "1" (note you have to check for the character 1, not  number

1, hence the "").

So for bit 1 you would have

``(Byte0 as text) => if Text.ToList( Byte0 ){6} = "1" then "BIT 1" else ""``

for bit 3

``(Byte0 as text) => if Text.ToList( Byte0 ){4} = "1" then "BIT 3" else ""``

etc

Note the number for accessing the different positions in the string is inverted vs what you were using

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors