Power Query: Using Recursion to Solve Hex to Decimal Conversion
Introduction
Neither Power Query nor DAX supports Excel functions for performing alternative number base conversions to decimal, such as HEX2DEC. Solving this issue in Power Query or DAX becomes problematic due to the lack of traditional looping capabilities within these languages. However, custom functions coupled with a somewhat little-known capability of Power Query's "M" language, recursion, can help us solve this problem.
Base Function
The first function that we will need is a way to translate hexadecimal values or other bases to decimal. We can do this with a custom function, fnHex2Dec like below:
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
This function takes a single input parameter, a single text character and translates it to a decimal equivalent. You can test this function by clicking the "Invoke" button in the Power Query Editor window. Be sure to enter a single value preceded by a single quote, such as 'A. The single quote forces the input to be recognized as text. This ensures that if you enter a 7, it is recognized as text instead of a number.
Abject Failure
For our first attempt, we create a second function that leverages our first function. We call this function fnHex2Dec2
let
fnHex2Dec2 = (input, base) =>
let
Reverse = List.Reverse(Text.ToList(input)),
DecimalValues = List.Transform(Reverse, each List.First(fnHex2Dec(_)) * Number.Power(base,List.PositionOf(Reverse,_))),
Return = List.Sum(DecimalValues)
in
Return
in
fnHex2Dec2
This function takes two parameters, the text of the hexadecimal number to convert and the base from which we are converting (for hexadecimal, 16). The sixth line reverses the text by converting the text to a list and then using the List. Reverse function. The next line transforms each element in the list by calling our function to translate the text character and multiplying by the appropriate power of the base. This line seeks to use the position of the element in the list to determine the correct power. The next line then sums the elements of the list.
Invoking this function with '7D9A and 16 returns the decimal value 32154, which is correct. However, entering the value '7777 and 16 returns the value 28 which is most certainly not correct, the correct value is 30,583. So what is going on here? Unfortunately, the List.PositionOf function finds the first reference to the character in the List, so since the number 7 is repeated, each transformation of the list finds the first occurrence in position 0 of the list and thus each transformation returns 7 (7 * 16^0). Not what we were looking for.
Solution
In order to solve this problem, we need a way to return the correct position of each item in the list when we are determining the correct power to use during each calculation. Because Power Query does not have constructs like a for or next loop in which we could use a counter, we must find another solution. Here is where recursion comes to the rescue. We can construct a simple function like the one below-called fnHex2Dec3:
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
This function takes four input parameters, a List of text characters, numeric base, a numeric counter which must start at -1 and a numeric variable for summing which must start at 0. Thus, to call this function, one would use 'A, 16, -1, 0 in order to convert the hexadecimal "A" character to 10. The function really only consists of a single if statement. The true/false portion of the if statement checks to see if the end of the List has been reached and if so, returns the calculation for the last element of the list. If the end of the list has not been reached, then the function adds the summing variable "start" to the result of a recursive call using the @function syntax to refer to itself. This recursive call passes in the same input List, and base but increments the counter by 1 and provides a new "start" value, which is the result of the same base to decimal conversion mathematics as before.
This function could potentially stand on its own. However, if we were attempting to convert 7D9A, we would have to enter it into this function as 'A9D7 as well as put in the base 16 and know to start our counter at -1 and our starting value as 0. And, even if we did all of this, we would still get an error, because the function is expecting a List data type for input, not Text. Thus, in order to simplify the use of this function and make it useful, we can create a simple helper function, fnHex2Dec4
let
fnHex2Dec4 = (input, base) =>
let
Reverse = List.Reverse(Text.ToList(input)),
Return = fnHex2Dec3(Reverse,base,-1,0)
in
Return
in
fnHex2Dec4
This function only takes two parameters, the input text and the base from which we are converting. The sixth line converts the text to a list, reverses it for us while the next line initiates the recursive function fnHex2Dec3 and seeds it with the correct starting values for all parameters.
The result is that given a column "Hex" that contains hexadecimal values, we can create a custom column with the formula:
=fnHex2Dec4([Hex],16)
We now have a single, easy function call that replicates the functionality of HEX2DEC in Excel. Even better, we can use this for other base conversions such as binary to decimal or octal to decimal by using 2 or 8 for the second parameter.
Conclusion
There is no easy, single function for converting alternative base values, such as hexadecimal, to decimal. However, through the use of the recursive capabilities of Power Query's "M" language, we can create some simple functions that replicate this capability.
http://c.statcounter.com/10909595/0/4022c2f5/1/