Power Query – Converting a hexadecimal number to decimal

There was an excellent blog post by Greg Deckler a few weeks back entitled Using Recursion to Solve Hex to Decimal Conversion. I thoroughly recommend reading to get some additional context.

However, I thought I’d try an alternative approach to converting hex to decimal that does not use recursion.

Converting a hexadecimal value into decimal in Power Query is actually pretty straightforward, for certain test cases.

For example, you can write:

And this will return the correct answer of 32154.

Which is great – but of course, it will only work for hex value comprising 4 characters.

So I’ve produced a more generalised solution:

Let me know if you would like a walkthrough of exactly how this works.

6 thoughts on “Power Query – Converting a hexadecimal number to decimal”

  1. Hi Ken
    Good job 🙂 Thanks.
    Personally I prefer different approach to the problem. Something like this below.
    let
    HexString = “9A1BCDF”,
    HexLst = List.Buffer({“0″..”9″,”A”..”F”}),
    OneDigitHexToDec = List.Transform( List.Reverse(Text.ToList(Text.From(HexString))), each List.PositionOf(HexLst,_)),
    DecValue = List.Sum(List.Transform(List.Positions(OneDigitHexToDec), each Number.Power(16,_)*OneDigitHexToDec{_}))
    in
    DecValue
    We can easy covert it to a function. Additionally it could be use to convert from many different systems to decimal (just swap “16” to another number)

    Regards

    1. Nice and elegant solution, Bill (as always).
      Here is the function from your code:

      (input, base as number) as number =>
      let
      HexLst = List.Buffer({“0″..”9″,”A”..”F”}),
      OneDigitHexToDec = List.Transform(List.Reverse(Text.ToList(Text.From(input))), each List.PositionOf(HexLst,_)),
      DecValue = List.Sum(List.Transform(List.Positions(OneDigitHexToDec), each Number.Power(base,_)*OneDigitHexToDec{_}))
      in
      DecValue

  2. I think that I am running into the same issue that I did in Excel – the 15 digit limitation
    I’d like to convert Hex:
    30342DB7A81939C01820001E
    to decimal and to binary.

  3. I’ve tried both. They both seem to be susceptible to a rounding error similar to what happens in Excel. Excel drops all digits after 15.

Leave a Reply

Your email address will not be published. Required fields are marked *