Forum Discussion

JFAschoon's avatar
JFAschoon
Icon for Helper I rankHelper I
2 years ago

Converting Hexadecimal to Decimal

Good all.

 

Ive got the following syntex to convert a 2 digit  HexaDecimal(2E) to Decimal value which works 100% fine.

Know im sitting with a 4 digit HexaDecimal(d2da) in a column which i would like to use the same syntex but only modifying it to be able to do 4 hexadecimal digits.Ive Tried but failed badly. See example syntex below

 

HexToDecimal =

VAR _FirstHexDigit = RIGHT('CM18'[Hex],1)
VAR _SecondHexDigit = MID('CM18'[Hex],3,1)


RETURN

LOOKUPVALUE('HexTable'[Dec],'HexTable'[Hex], _SecondHexDigit)*16 +LOOKUPVALUE('HexTable'[Dec],'HexTable'[Hex], _FirstHexDigit)

 

thanks 

 

Regards 

Johan

Need Help

7 Replies

  • hnguy71's avatar
    hnguy71
    Icon for Super User rankSuper User
    2 years ago

    JFAschoon ,

     

    I believe it would be easier and more dynamic if you did it inside power query.

     

    Since you already have a hex conversion table, within PowerQuery should be easy enough:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnV0UorViVYyMwBTJgaGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hex = _t]),

    // Hex to Decimal Conversion Starts Here
    ConvertHex = Table.AddColumn(Source, "ConvertHex", each 
    Table.Group(
        Table.AddColumn(
            Table.AddColumn(
                Table.ExpandTableColumn(
                    Table.NestedJoin(
                        Table.AddIndexColumn(
                            Table.FromList(
                                Text.ToList(Text.Reverse([Hex]))
                                ), 
                            "Index", 0, 1, Int64.Type
                        ), 
                    {"Column1"}, HexTable, {"ID"}, "HexTable", JoinKind.LeftOuter
                    ), 
                "HexTable", {"Value"}, {"Value"}), 
                "Group", each 1
            ),
        "AddMeUp", 
        each Number.Power( 16, [Index]) * [Value]
        ),
    {"Group"}, {{"Decimal", each List.Sum([AddMeUp]), type number}})),
    ToDecimal = Table.ExpandTableColumn(ConvertHex, "ConvertHex", {"Decimal"}, {"Decimal"})
in
    ToDecimal

     

    Sample Output:

     

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    I wrote you a function to convert HEX to decimal

    (HextoDecimal) =>
  List.Sum(
    Table.AddColumn(
      
         [
          ListDic=
          [
            t = Splitter.SplitTextByRepeatedLengths(1)(HextoDecimal), 
            dict = List.Buffer(
              {
                {"0", "0"}, 
                {"1", "1"}, 
                {"2", "2"}, 
                {"3", "3"}, 
                {"4", "4"}, 
                {"5", "5"}, 
                {"6", "6"}, 
                {"7", "7"}, 
                {"8", "8"}, 
                {"9", "9"}, 
                {"A", "10"}, 
                {"B", "11"}, 
                {"C", "12"}, 
                {"D", "13"}, 
                {"E", "14"}, 
                {"F", "15"}
              }
            ), 
            f = (x) => List.Accumulate(dict, x, (s, c) => Text.Replace(s, c{0}, c{1})), 
            to = List.Transform(t, (x) => f(x))
          ][to],
          ListPower= 
          List.Generate( () => List.Count(ListDic)-1,
               (x)=> x >= 0,
               (x)=> x-1),
              ListDecimal= Table.FromColumns({ListDic,ListPower},{"decimal", "Power"})][ListDecimal]
        
      , 
      "DecimalSum", 
      each Number.From([decimal]) * Number.Power(16, [Power])
    )[DecimalSum]
  )


    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
    HextoDecimal.pbix

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    updated fanction

     

    (HextoDecimal) =>
  List.Sum(
    [
      t = Splitter.SplitTextByRepeatedLengths(1)(HextoDecimal),
      dict = List.Buffer(
        {
          {"0", "0"},
          {"1", "1"},
          {"2", "2"},
          {"3", "3"},
          {"4", "4"},
          {"5", "5"},
          {"6", "6"},
          {"7", "7"},
          {"8", "8"},
          {"9", "9"},
          {"A", "10"},
          {"B", "11"},
          {"C", "12"},
          {"D", "13"},
          {"E", "14"},
          {"F", "15"}
        }
      ),
      f = (x) => List.Accumulate(dict, x, (s, c) => Text.Replace(s, c{0}, c{1})),
      ListTrans = List.Transform(t, (x) => f(x)),
      ListPower = List.Generate(() => List.Count(t) - 1, (x) => x >= 0, (x) => x - 1),
      ListDecimal = Table.FromColumns({ListTrans, ListPower}, {"decimal", "Power"}),
      addColumn = Table.AddColumn(
        ListDecimal,
        "DecimalSum",
        each Number.From([decimal]) * Number.Power(16, [Power])
      )[DecimalSum]
    ][addColumn]
  )