convert from hexadecimal value to number

[BIXL]

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 : 7D9A and after converting it from hex to number I'm expecting to get : 32154

Thanks

[brent_railey]

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

[Greg_Deckler]

Well, I'm sure somebody else has a better method, but this might get you pointed down the right track. First, create a blank query.

let
    fnHex2Dec = (input) =>

let

values = {

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

Then, you can split out your hex number based upon 1 character each and create columns like the following:

try [Hex.1]*Number.Power(16,3) otherwise fnHex2Dec([Hex.1]){0} * Number.Power(16,3)

And then just add them all together. Here is the full text of the 2nd query where I used your number and got the right answer:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnexdFSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Hex = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hex", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type","Hex",Splitter.SplitTextByRepeatedLengths(1),{"Hex.1", "Hex.2", "Hex.3", "Hex.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Hex.1", Int64.Type}, {"Hex.2", type text}, {"Hex.3", Int64.Type}, {"Hex.4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Dec.1", each try [Hex.1]*Number.Power(16,3) otherwise fnHex2Dec([Hex.1]){0} * Number.Power(16,3)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Dec.2", each try [Hex.2]*Number.Power(16,2) otherwise fnHex2Dec([Hex.2]){0} * Number.Power(16,2)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Dec.3", each try [Hex.3]*Number.Power(16,1) otherwise fnHex2Dec([Hex.3]){0} * Number.Power(16,1)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Dec.4", each try [Hex.4]*Number.Power(16,0) otherwise fnHex2Dec([Hex.4]){0} * Number.Power(16,0)),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Decimal", each [Dec.1]+[Dec.2]+[Dec.3]+[Dec.4])
in
    #"Added Custom4"

I'm sure that you could probably write a single function in M to convert your hex to decimal. If I have some additional time, I'll take a look at doing that all within a single M function. Probably could iterate over the input list and do some calculations to get the right answer.

[Sean]

Don't you wish that external file was Excel? Would save you lot of trouble.

I knew they added added more functions to DAX - so I tried HEX2DEC( ) - Nope. Sorry! Not yet.

[Greg_Deckler · Accepted solution]

@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.

[BIXL]

Hi Smoupre,

Thanks for the detailed solution.

It's seems that the first function is missing the "0" convert. Without  it it gives an error when trying to convert values containing zero.

beside that it's all good !

thanks again.

[Greg_Deckler]

Good catch. **bleep** details.

[Greg_Deckler]

@BIXL - I found another bug in this that I am working on. Basically, if you have the same number in more than once, the PositionOf function is returning the position of the first one for all of them. So, I need to figure out a way to stop that from happening. Man, I wish I had a freaking for loop or something with a counter.

[Greg_Deckler]

@BIXL - OK, I believe I have the *real* solution!  I implemented this in 3 functions, one of which is recursive. This improved version will handle different bases as well as base-16. First, the standard function with the addition of zero mapping:

let
    fnHex2Dec = (input) =>

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}=input)){1}},type {number})

in

Result
in
    fnHex2Dec

Next, there is the recursive function that does most of the work:

let 
    fnHex2Dec3 = (input,base,counter,start) => if counter = (List.Count(input) - 1) then List.First(fnHex2Dec(input{counter}))*Number.Power(base,counter) else start + @fnHex2Dec3(input,base,counter+1,List.First(fnHex2Dec(input{counter+1})) * Number.Power(base,counter+1))
in
    fnHex2Dec3

Finally, there is your "input" function, it takes two parameters, your text number input and what base you want. If you want to invoke the function for testing purposes, then you can use a format for the input parameter like '7D9A or '7777. The ' forces the input to be recognized as text.

let
    fnHex2Dec4 = (input, base) =>

let

Reverse = List.Reverse(Text.ToList(input)),
Return = fnHex2Dec3(Reverse,base,-1,0)

in

Return
in
    fnHex2Dec4

[GarryFarrell]

Hi,

Thanks for the functions. How do I add the functions to the pbix file? Do I add them all together or separately. I found it difficult to find a document to explain how to setup the functions.

So far I have added a new blank source and opened the advance editor and pasted the code for fnHex2Dec. If this is the way to implement the functions, I then have trouble with the names already being used. So I had to change the name of the function in the query section which breaks the code for your other functions.

[GarryFarrell]

Hi,

Thanks for the functions. How do I add the functions to the pbix file?

So far I have added a new blank source and opened the advance editor and pasted the code for fnHex2Dec.

[Greg_Deckler]

@BIXL - OK, I believe I have the *real* solution!  I implemented this in 3 functions, one of which is recursive. This improved version will handle different bases as well as base-16. First, the standard function with the addition of zero mapping:

let
    fnHex2Dec = (input) =>

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}=input)){1}},type {number})

in

Result
in
    fnHex2Dec

Next, there is the recursive function that does most of the work:

let 
    fnHex2Dec3 = (input,base,counter,start) => if counter = (List.Count(input) - 1) then List.First(fnHex2Dec(input{counter}))*Number.Power(base,counter) else start + @fnHex2Dec3(input,base,counter+1,List.First(fnHex2Dec(input{counter+1})) * Number.Power(base,counter+1))
in
    fnHex2Dec3

Finally, there is your "input" function, it takes two parameters, your text number input and what base you want. If you want to invoke the function for testing purposes, then you can use a format for the input parameter like '7D9A or '7777. The ' forces the input to be recognized as text.

let
    fnHex2Dec4 = (input, base) =>

let

Reverse = List.Reverse(Text.ToList(input)),
Return = fnHex2Dec3(Reverse,base,-1,0)

in

Return
in
    fnHex2Dec4