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:

1 2 3 4 5 6 7 8 9 10 |
let HexString = "7D9A", BinaryValue = Binary.FromText(HexString, BinaryEncoding.Hex), DecimalValue = BinaryFormat.SignedInteger16(BinaryValue) in DecimalValue |

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
let HexString = "7D9A", InitialStringLength = Text.Length(HexString), PaddedHexString = if Number.Mod(InitialStringLength, 2) = 0 then HexString else "0" & HexString, StringLength = Text.Length(PaddedHexString), WhereToSplit = List.Numbers(2, StringLength/2, 0), Split = Splitter.SplitTextByLengths(WhereToSplit)(PaddedHexString), MyResult = List.Generate( () => [Pos=0, DecimalValuesToAdd = 0, Pow=StringLength-2], each [Pos] < List.Count(Split)+1, each [ DecimalValuesToAdd = [DecimalValuesToAdd] + BinaryFormat.Byte(Binary.FromText(Split{[Pos]}, BinaryEncoding.Hex)) * Number.Power(16,[Pow]), Pos = [Pos]+1, Pow = [Pow]-2 ], each [DecimalValuesToAdd]), DecimalResult = List.Last(MyResult) in DecimalResult |

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

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

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

Maybe I’m not getting something.

Wouldn’t one line of code suffice?

= Expression.Evaluate(“0x”&HexString)

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.

Hi Scott, which approach are you using and what is the error?

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.