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 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.
Community ChampionBIXL - 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
- GarryFarrell
Advocate III
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.
- 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.
