Search code examples
powerbidaxpowerquerympowerbi-datasource

Converting Decimal to Binary Power bi

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

Here is how my data looks like

enter image description here

Solution

  • You can do it with recursion in Power Query / M.

    Bin = (t as text, n as number) => 
    if n <= 1 
    then Text.From(n) & t
    else @Bin(Text.From(Number.Mod(n, 2)) & t, Number.RoundDown(n/2))

    Note the @ before the Bin which enables the recursion to happen.

    Results:

    converted

    My query for your reference:

    let
    Bin = (t as text, n as number) => if n <= 1 then Text.From(n) & t else @Bin(Text.From(Number.Mod(n, 2)) & t, Number.RoundDown(n/2)),

    Source = {0..14},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Config"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Config", Int64.Type}}),
    #"Added New Column" = Table.AddColumn(#"Changed Type", "Config2", each Bin("", [Config]))
in
    #"Added New Column"

    If you need to pad leading zeros to Config2 then you'll need the following DAX:

    Formatted Config2 = FORMAT(VALUE(Query1[Config2]), "0000")

    Results:

    padded