A while ago I wrote a blogpost on how to use Unicode characters in Power BI. In that blogpost I used a recursive Power Query function to convert Hex values to Dec values. A few weeks back one of my site visitors kindly shared his non-recursive version of Power Query function which beautifully does the job. A big shout out to Rocco Lupoi for sharing his code. So, I decided to share it with everyone so more people can leverage his nice Power Query function. I have touched his code a bit though, but it was more of a cosmetic change, so all credits of this blogpost goes to Rocco. The benefits of his code is not limited to being non-recursive. **The code below converts numbers of any base when the base is smaller than 16 like Binary and Oct, so it is not limited to Hex values** **only**. The other benefit of the below code is that it is not case sensitive (note to the step on the code below).

`digits `

Here is the function for Power Query:

`fnHex2Dec `

```
(input as text, optional base as number) as number =>
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
],
digits = Text.ToList(Text.Upper(input)),
dim = List.Count(digits)-1,
exp = if base=null then 16 else base,
Result = List.Sum(
List.Transform(
{0..dim}
, each Record.Field(values, digits{_}) * Number.Power(exp, dim - _)
)
)
in
Result
```

As you see in the code above, the

parameter is optional, so if not provided base 16 would be the default.*base *

This is how we can invoke the above function:

`fnHex2Dec("AbCdEf", null)`

Here is the results of invoking the * fnHex2Dec* function to convert binary to decimal:

`fnHex2Dec("101010111100110111101111", 2)`

And this one how it works to convert Oct to decimal:

`fnHex2Dec("52746757", 8)`

What do you think about the function above? Leave your thoughts in the comments section below.

If you want to avoid complex code, Expression.Evaluate may be good enough for you: https://xxlbi.com/blog/converting-hexadecimal-to-decimal-numbers-in-power-query/

Hi Daniil,

Welcome to BIInsight.com and thanks for your comment.

Yes, the

also does the job, but I saw Curt’s comment on Chris Webb’s blog back in 2018 about potential problem in some cases.`Expression.Evaluate()`

Besides, the fact that this function can indeed convert any other bases (if smaller than 16) as well as Hex is a big plus.

Not too sure if the

can also handle binary and Oct though.`Expression.Evaluate()`

So, I think the function provided in this post still has a place in our Power BI/Power Query toolbelt.

Cheers.

What about that code?

(hexa as text) as nullable number =>

let

Source = Text.PositionOf(“0123456789abcdef”, Text.Lower(Text.End(hexa, 1))),

Value = if Text.Length(hexa) > 1 then Source + HexValue(Text.Start(hexa, Text.Length(hexa)-1 )) * 16 else Source

in

Value

or if you want a bas you can use (not tested thought)

(hexa as text, base as number) as nullable number =>

let

Source = Text.PositionOf(“0123456789abcdef”, Text.Lower(Text.End(hexa, 1))),

Value = if Text.Length(hexa) > 1 then Source + HexValue(Text.Start(hexa, Text.Length(hexa)-1 ), base) * base else Source

in

Value