Converting hexadecimal to decimal numbers in Power Query

On more than one occasion I saw people writing complex functions in M to convert hexadecimal numbers to decimal ones (Greg Deckler and Soheil Bakhshi, to name a few). In this blog post, I’d like to share probably the quickest way of doing it.
The solution really is simple! Let’s say you’re interested in converting a4b3f0 into a decimal number. Then you should do it like so:

Expression.Evaluate("0x" & "a4b3f0")

And it works!

Just replace the yellow bit with your hexadecimal number. If you must have a function, here’s one way of writing it:

(Hex as text) as nullable number =>

try Expression.Evaluate("0x" & Hex) otherwise null

The solution isn’t mine — I first saw Igor Cotruta suggesting it. Unfortunately, the website where I saw the solution is no longer online, so I can’t provide a link.

If you’ve got a programming background, you may have reservations about using Expression.Evaluate. I asked Chris Webb whether there are any negative implications, and Curt Hagenlocher from the Power Query team was kind enough to reply:

The Power BI service still relies on static analysis for data source discovery, so doing data access via Expression.Evaluate can be problematic in that context. There’s no other specific concern I can think of; the use of #shared is probably a bigger “future risk” than Expression.Evaluate (though of course the two are often used together).

Update 1 August 2020: For other bases, such as binary, you still need to write longer code, as far as I’m aware. I wrote my version too just for fun:

(Input as text, optional Base as number) as number =>
    Symbols = {"0".."9"} & {"A".."F"},
    AsReverseList = List.Reverse(Text.ToList(Text.Upper(Input))),
    AsNumbers = List.Transform(
        each List.PositionOf(Symbols, _)
    DigitConversion = List.Generate(
        () => 0,
        each _ < List.Count(AsNumbers),
        each _ + 1,
        each AsNumbers{_} * Number.Power(Base ?? 16, _)
    Result = try List.Sum(DigitConversion) otherwise null

Note how I’m using the null coalescing operator (??), which I’ve learned about from Ben Gribaudo.

Now it’s up to you which method to use — happy conversions!

  • Nice post Daniil and thanks for the referral.
    I fail to use the Expression.Evaluate() to convert bases other than 16 (binary, Oct).
    Can you elaborate please?

    • Hi, Soheil, you’re right — I wasn’t thinking straight last night, thanks for catching this! Updated my blog accordingly.

  • Hugo Toledo

    Excellent content, as always. Thank you!

    You mentioned a website no longer being online. Did you try the Wayback Machine at the Internet Archive? If they have crawled it you can provide the resulting URL here.