A solution to convert Decimal to Hex

[absherzad]

Hi all,
I am new to Power Query, I needed to convert Decimal to Hex, here is my solution using recursive approach.
It is working like a charm, however, I have no idea if it is an optimal solution. So, comments are appreciated:

(input as number, optional string as nullable text) => 
let
	inputString = if string = null then "" else string,
	reminder = Number.Mod(input, 16),
        
	helperString = if reminder > 9 then
            List.First(List.Select({ {10, "A"}, {11, "B"}, {12, "C"}, {13, "D"}, {14, "E"}, {15, "F"} }, each _{0} = reminder)){1} & "," & inputString
        else
            Text.From(reminder) & "," & inputString,
	
	quotient = Number.IntegerDivide(input, 16),
	
	finalResult = if quotient > 0 then
		@fxNumberToHex(quotient, helperString)
		else List.Accumulate(Text.Split(helperString, ","), "", (state, current) => Text.From(state) & Text.From(current) & "")
in
	finalResult

 

[MrKowz]

I'm no expert on the conversion methodology, but is there a reason you can't use the worksheet formula DEX2HEX()?

See Converting Decimal To HexaDecimal In VBA

 

[absherzad]

Yes in Microsoft Excel, VBA, Java and other programming languages it is relatively easy and straight-forward. But I wanted to try that in Power Query because I had to for some reason. Therefore, I came across the solution I posted. I wanted to share with others as well as to receive others feedback for its improvement.

 

[MarcelBeug]

Some time ago I created the function below, that will convert a number to any base string (binary, octal, hexadecimal, base 32, base 64) or a string to a number, given a base (16 for hexadecimal).
It uses lists instead of recursion.
In case of converting a number to a base, a minimum output length can be supplied.
The function includes full documentation. Results look good to me, but I can't give any guarantees.

let 
    Documentation =
    [Documentation.Category =    "Custom Function",
     Documentation.Description = "Function created by Bemint to convert numbers from or to a specific base",
     Documentation.Examples =    { [Description = "Convert 10 to binary",
                                   Code = "fnNumberBaseConversion(10, 2)",
                                   Result = "10100"],
                                   [Description = "Convert 123456 to hexadecimal with a result length of minimal 8",
                                   Code = "fnNumberBaseConversion(123456, 16, 8)",
                                   Result = "0001E240"]},
     Documentation.LongDescription =
                "fnNumberBaseConversion converts a number to a base string or vice versa.
                 Supported bases are 2 thru 16, 32 and 64.
                 Input is either an integer > 0 that will be converted to a base string or a base string that will be converted to a number.
                 Optionally, outputlength can be supplied to have resulting base strings padded to the desired length"],

    fnNBC = (input as anynonnull, base as number, optional outputlength as number) as any =>
    let
        //    input = 10,
        //    base = 2,
        //    outputlength = null,
        Base16 = "0123456789ABCDEF",
        Base32 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ2345567",
        Base64 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/",
        Lookups = List.Zip({{16,32,64},{Base16,Base32,Base64}}),
        Lookup = Text.ToList(List.Last(List.Select(Lookups,each _{0} <= List.Max({16, base}))){1}),
        InputToList = Text.ToList(input),
        Result = if input is text
        then
        let
            Reversed = List.Reverse(InputToList),
            BaseValues = List.Transform(Reversed, each List.PositionOf(Lookup,_)),
            Indexed = List.Zip({BaseValues, {0..Text.Length(input)-1}}),
            Powered = List.Transform(Indexed, each _{0}*Number.Power(base,_{1})),
            Decimal = List.Sum(Powered)
        in
            Decimal
        else
        let
            Elements = 1+Number.RoundDown(Number.Log(input,base),0),
            Powers = List.Transform(List.Reverse({0..Elements - 1}), each Number.Power(base,_)),
            ResultString = List.Accumulate(Powers,
                                          [Remainder = input,String = ""], 
                                          (c,p) => [Remainder = c[Remainder] - p * Number.RoundDown(c[Remainder] / p,0),
                                                    String = c[String] & Lookup{Number.RoundDown(c[Remainder]/p,0)}])[String],    
            PaddedResultString = if outputlength = null then ResultString else Text.PadStart(ResultString,outputlength,Lookup{0})
        in
            PaddedResultString
    in
        Result,
    // Create FunctionType with metadata at function type level:
    FunctionType = Type.ForFunction([ReturnType = type any, Parameters = [input = type anynonnull, base = type number, outputlength = type number]], 2) meta Documentation,
    fnNumberBaseConversion = Value.ReplaceType(fnNBC,FunctionType)
in
    fnNumberBaseConversion

 

[MarcelBeug]

Just noticed a small error in Base32: "5" is duplicated, so it should be:

Base32 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ234567",