Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago
Solved

Integer/Decimal to Binary in PQ

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 = 100010...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

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's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

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"