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

A Power Query Function to Convert HEX, OCT and BIN values to DEC

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)
Invoking fnHex2Dec function to convert numbers of any base to decimal

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

fnHex2Dec("101010111100110111101111", 2)
Power Query binary to decimal

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

fnHex2Dec("52746757", 8)
Power Query Oct to decimal

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

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

    1. 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.

Any thoughts? Share it with us here:

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