Forum Discussion
Resolver Iconvert from hexadecimal value to number
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 fnHex2Dec2nd 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
fnHex2Dec2Just 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.
Community ChampionBIXL - 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
fnHex2Dec2nd 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.
Resolver IHi 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 fnHex2DecNext, 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 fnHex2Dec3Finally, 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