cancel
Showing results for
Did you mean:
Frequent Visitor

## Converting Decimal number into Binary

My apologies I don't understand this.

https://community.powerbi.com/t5/Desktop/Number-to-Binary/m-p/235261#M104739

Here is my Data

I have a column name Config which contains decimal number 1 to 14. I want to create a new column config2 and convert config column into binary (base2).

eg. config2 =(config binary (base2))

*Config |Config2*

1 |0001

2 |0010

3 |0011

1 ACCEPTED SOLUTION
Microsoft

Hi @joyo,

To be honest, I don't understand it for now. But I can make it work.

1. Change a little to make it work as a function. (The code is quoted from this post: Number-to-Binary)

```let
fnNBC = (input as anynonnull, base as number, optional outputlength as number) as any =>
let
//    input = 10,
//    base = 2,
//    outputlength = null,
Base16 = "0123456789ABCDEF",
Base32 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ234567",
Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/",
Lookups = List.Zip({{16,32,64},{Base16,Base32,Base64}}),
Lookup = Text.ToList(List.Last(List.Select(Lookups,each _{0} <= List.Max({16, base}))){1}),
InputToList = Text.ToList(input),

// This part will be executed if input is text:
Reversed = List.Reverse(InputToList),
BaseValues = List.Transform(Reversed, each List.PositionOf(Lookup,_)),
Indexed = List.Zip({BaseValues, {0..Text.Length(input)-1}}),
Powered = List.Transform(Indexed, each _{0}*Number.Power(base,_{1})),
Decimal = List.Sum(Powered),
// So far this part

// This part will be executed if input is not text:
Elements = 1+Number.RoundDown(Number.Log(input,base),0),
Powers = List.Transform(List.Reverse({0..Elements - 1}), each Number.Power(base,_)),
ResultString = List.Accumulate(Powers,
[Remainder = input,String = ""],
(c,p) => [Remainder = c[Remainder] - p * Number.RoundDown(c[Remainder] / p,0),
String = c[String] & Lookup{Number.RoundDown(c[Remainder]/p,0)}])[String],
// So far this part

Result = if input is text then Decimal else PaddedResultString
in
Result
in
fnNBC```

2. Create a blank query, paste the code above. And change its name to "ToBinary".

`=Text.PadStart(ToBinary([Column1], 2), 4, "0")`

4. Done.

NOTE: The code above is quoted from https://community.powerbi.com/t5/Desktop/Number-to-Binary/m-p/235261#M104739

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft

Hi @joyo,

To be honest, I don't understand it for now. But I can make it work.

1. Change a little to make it work as a function. (The code is quoted from this post: Number-to-Binary)

```let
fnNBC = (input as anynonnull, base as number, optional outputlength as number) as any =>
let
//    input = 10,
//    base = 2,
//    outputlength = null,
Base16 = "0123456789ABCDEF",
Base32 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ234567",
Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/",
Lookups = List.Zip({{16,32,64},{Base16,Base32,Base64}}),
Lookup = Text.ToList(List.Last(List.Select(Lookups,each _{0} <= List.Max({16, base}))){1}),
InputToList = Text.ToList(input),

// This part will be executed if input is text:
Reversed = List.Reverse(InputToList),
BaseValues = List.Transform(Reversed, each List.PositionOf(Lookup,_)),
Indexed = List.Zip({BaseValues, {0..Text.Length(input)-1}}),
Powered = List.Transform(Indexed, each _{0}*Number.Power(base,_{1})),
Decimal = List.Sum(Powered),
// So far this part

// This part will be executed if input is not text:
Elements = 1+Number.RoundDown(Number.Log(input,base),0),
Powers = List.Transform(List.Reverse({0..Elements - 1}), each Number.Power(base,_)),
ResultString = List.Accumulate(Powers,
[Remainder = input,String = ""],
(c,p) => [Remainder = c[Remainder] - p * Number.RoundDown(c[Remainder] / p,0),
String = c[String] & Lookup{Number.RoundDown(c[Remainder]/p,0)}])[String],
// So far this part

Result = if input is text then Decimal else PaddedResultString
in
Result
in
fnNBC```

2. Create a blank query, paste the code above. And change its name to "ToBinary".

`=Text.PadStart(ToBinary([Column1], 2), 4, "0")`

4. Done.

NOTE: The code above is quoted from https://community.powerbi.com/t5/Desktop/Number-to-Binary/m-p/235261#M104739

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

Hello,

This throws an error if we try to convert "0" to binary.... Can we update the solution to handle zero?

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors