Forum Discussion

BIXL's avatar
BIXL
Icon for Resolver I rankResolver I
10 years ago
Solved

convert from hexadecimal value to number

Hello, I am looking for a way to get an hexadecimal value from external file and in Power Query to convert it to a number. can't find the proper function for it...   for example the file value is...
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    BIXL - I created 2 functions that allow you to simply pass a hexadecimal value to fnHex2Dec2 in order to convert it to Decimal.

     

    First one is basically the same as before:

    let
    fnHex2Dec = (input) =>

let

values = {

{"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}

},

Result = Value.ReplaceType({List.First(List.Select(values, each _{0}=input)){1}},type {number})

in

Result
in
    fnHex2Dec

    2nd one is the one that you want to pass in your hex value to:

     

    let
     fnHex2Dec2 = (input) =>

    let

    Reverse = List.Reverse(Text.ToList(input)),
    DecimalValues = List.Transform(Reverse, each List.First(fnHex2Dec(_)) * Number.Power(16,List.PositionOf(Reverse,_))),
    Return = List.Sum(DecimalValues)

    in

    Return
    in
     fnHex2Dec2

    Just makes sure that your input column is of type Text and then you can add a custom column like:

    =fnHex2Dec2([Hex])

    And you will get back the decimal value.

brent_railey's avatar
brent_railey
New Member
6 years ago

I took the above and got it all into one function and validated: 

 

Hex2Dec = (input as text) =>
let
    convertHexDigit = (digit) =>
    let
        values = {
            {"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}
        },
        Result = Value.ReplaceType(
            {List.First(List.Select(values, each _{0} = digit)){1}}, 
            type {number}
        )
    in
        Result,
    Reverse = List.Reverse(Text.ToList(input)) // In greatest to smallest,
    noDigits = List.Numbers(0, List.Count(Reverse) - 1) // Order of magnitude,
    DecimalValues = List.Transform(noDigits, each List.First(convertHexDigit(Reverse{_})) * Number.Power(16, _)),
    Return = List.Sum(DecimalValues)
in 
    Return