Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Greg_Deckler

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, that 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.

Comments

Added tracking counter.

 

 

Hi Greg,

 

I've tried to tackle this from another angle, without recursion.

 

Power Query – Converting a hexadecimal number to decimal

 

The solution relies on using Binary.FromText to convert the Hexadecimal string to Binarry.

 

It then uses BinaryFormat to convert to a decimal value.

 

Cheers

 

Ken

 

= Expression.Evaluate(“0x”&input)

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. How do I add the other functions. How do I name the queries and the functions?

Yes, so basically you repeat that process for the other functions. I generally create a "Blank Query" and then write my function code and then be sure to name your query the name of your function to avoid confusion.

Thanks. I found the issue. I was creating a new query, then going to the advanced editor, pasting the code and saving. Then I would try to rename the query using the same name as the function and I would get an error saying that the name already existed. So now I create the blank query, rename it straight away, and then add the code and it all works.