cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joyo
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 

binary.PNG    

 

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
v-jiascu-msft
Microsoft
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],    
            PaddedResultString = if outputlength = null then ResultString else Text.PadStart(ResultString,outputlength,Lookup{0}),
        // 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".Converting Decimal number into Binary.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Add a custom column.

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

Converting Decimal number into Binary1.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

1 REPLY 1
v-jiascu-msft
Microsoft
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],    
            PaddedResultString = if outputlength = null then ResultString else Text.PadStart(ResultString,outputlength,Lookup{0}),
        // 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".Converting Decimal number into Binary.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. Add a custom column.

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

Converting Decimal number into Binary1.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors