Integer/Decimal to Binary in PQ

[smpa01]

Does PQ currently have any syntax that converts an integer /decimal value to Binary.

I looked into the documentation and could not find anything

e.g.

     52 = 110100,  

     137.5879541 = 10001001.10010110100001000010100011101111000011001001

https://www.rapidtables.com/convert/number/decimal-to-binary.html

Thank you in advance

@ImkeF 

[mahoneypat]

Thought about this some more and came up with the function below to convert any integer to binary.  You can create a blank query, put this is the advanced editor.  On your original query, you can then Invoke a Custom Function on the add column tab.

(inputnumber)=>
let
    input = inputnumber,
    digits = Number.RoundUp(Number.Log(input, 2), 0),
    powerslist = List.Transform(List.Reverse({0..digits}), each Number.Power(2,_)),
    oneszeros = List.Accumulate(powerslist, [rem = input, num = {}], (state, current) => if state[rem] >= current then [rem = state[rem] - current, num = state[num] & {1}] else [rem = state[rem], num = state[num] & {0}]),
    listastext = List.Transform(oneszeros[num], each Number.ToText(_)),
    result = Text.Combine(listastext, "")
in
    result

@Ehren had a solution that was even better.

Converting Number column to binary and map the bin... - Microsoft Power BI Community

Pat

[smpa01 · Accepted solution]

@mahoneypat  this is mine and only for integer but I even have a better solution than this, getting blogged out.

let
  Source = (p1 as number) =>
    let
      Loop = List.Generate(
        () => [i = p1, j = Number.IntegerDivide(i, 2), k = Number.Mod(i, 2), l = Text.From(k)], 
        each  [i] > 0, 
        each  [i = [j], j = Number.IntegerDivide(i, 2), k = Number.Mod(i, 2), l = Text.From(k) & [l]], 
        each  [l]
      )
    in
      Loop{List.Count(Loop) - 1},
   Source1 = {1..100},
    #"Converted to Table" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each fx([Column1]))    
in
  #"Added Custom"

[mahoneypat]

I am not aware of a native function to do that.  However, you could do it with a custom function, R or Python, or with a REST API call (slower).

Pat

[BA_Pete]

Hi @smpa01 ,

Read through this thread:

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

Here, I believe, is the final iteration of the solution function courtesy of, and much credit to, M-Wizard @MarcelBeug :

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(List.Max({1,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

Pete

[smpa01]

@BA_Pete  thanks for looking into it.  

@MarcelBeug is a legend in this forum and I did come across this post. But I did not want to utilize this as I can't imagine running this on a big table as the performance would take a big hit. Also, it does not convert the decimal value to  binary.

I have a new solution and blog out soon.

@mahoneypat  many thanks for the confirmation.