# Quick Tips: Converting Hexadecimal, Oct and Binary to Decimal in a Single Power Query Function

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 `digits `step on the code below).

Here is the `fnHex2Dec `function for Power Query:

``````(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 `base `parameter is optional, so if not provided base 16 would be the default.

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)``

## 3 thoughts on “Quick Tips: Converting Hexadecimal, Oct and Binary to Decimal in a Single Power Query Function”

1. Soheil Bakhshi says:

Hi Daniil,

Welcome to BIInsight.com and thanks for your comment.
Yes, the `Expression.Evaluate()` also does the job, but I saw Curt’s comment on Chris Webb’s blog back in 2018 about potential problem in some cases.
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 `Expression.Evaluate()` can also handle binary and Oct though.
So, I think the function provided in this post still has a place in our Power BI/Power Query toolbelt.

Cheers.

1. Pascal says:

(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

This site uses Akismet to reduce spam. Learn how your comment data is processed.