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 =>
let
    Symbols = {"0".."9"} & {"A".."F"},
    AsReverseList = List.Reverse(Text.ToList(Text.Upper(Input))),
    AsNumbers = List.Transform(
        AsReverseList,
        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
in
    Result

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!