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)