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
Solved! Go to Solution.
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".
3. Add a custom column.
=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
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".
3. Add a custom column.
=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
Hello,
This throws an error if we try to convert "0" to binary.... Can we update the solution to handle zero?
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
User | Count |
---|---|
241 | |
56 | |
49 | |
45 | |
44 |
User | Count |
---|---|
280 | |
211 | |
82 | |
76 | |
74 |