Function convert a number from given base to base 10.

[Anonymous]

The following function converts a number from a certain
base (<16) in the corresponding base 10 value.
I would like to test to possibly improve the management of particular cases that I have not considered.

#########edited################

let
    fnBase2Dec = (input, base) =>
 
let
 
values = [
 
0=0,
1=1,
2=2,
3=3,
4=4,
5=5,
6=6,
7=7,
8=8,
9=9,
A=10,
B=11,
C=12,
D=13,
E=14,
F=15

],

digits=Text.ToList(Text.Upper (input)),
dim=List.Count(digits)-1,
exp=if base=null then 16 else base,
fields=Record.FieldNames(values),
values_base=Record.SelectFields(values, List.FirstN(fields, exp)),
Result = List.Sum(List.Transform({0..dim}, each Record.Field(values_base,digits{_})*Number.Power(exp,dim - _)))

 
in
 
Result

 
in
    fnBase2Dec

[Smauro]

Alright, I like this

Ideas:

1) you should set base as optional:

(input as anynonnull, optional base as number) =>

2) You could add an extra step to transform the input to text in case it's a number (case base2 to base9):

digits = Text.ToList(Text.Upper(Text.From(input))),

 3) you could stop it if it's base0 or 1:

exp=if base=null then 16 else base,

Result = if exp < 2 or exp >16 then Error.Record("Base not accepted", "Base given: " & Text.From(exp))
else let
digits=Text.ToList(Text.Upper (input)),
dim=List.Count(digits)-1,
fields=Record.FieldNames(values),
values_base=Record.SelectFields(values, List.FirstN(fields, exp)
in List.Sum(List.Transform({0..dim}, each Record.Field(values_base,digits{_})*Number.Power(exp,dim - _)))

 
in
 
Result

4) you could actually not do 3 and change the function's type with Documentation.AllowedValues:

let
fn =  .....

, fnType =Type.FroFunction([ReturnType = type any,
Parameters = [input = type anynonnull,
base = type number meta [Documentation.AllowedValues = {2..16}]]],1),

Replace = Value.ReplaceType(fn, fnType)
in Replace

5) You could fix the error message in case an out of bounds character came about:

NumberBase10 = try List.Sum....
Result = if NumberBase10[HasError] then Error.Record("Out of bounds Input", "Character: """ & Text.BetweenDelimiters(NumberBase10[Error][Message], "field '","' of the recor") & """ does not exist in base " & Text.From(exp), input) else NumberBase10[Value]

6) You could dynamically make the transformlist:

 values = let
 l = if exp < 11 then {"0"..Text.From(exp-1)} else List.Union({{"0".."9"}, List.FirstN({"A".."Z"}, exp-10)})
in Record.FromList({0..List.Count(l)-1}, l),

7) You could use List.Accumulate with List.Zip for no apparent reason:

List.Accumulate(List.Zip({digits, {0..dim}}), 0, (s, x) => s + Record.Field(values_base, x{0})*Number.Power(exp, dim - x{1}))

I think that's all I can think of.

Cheers

[Anonymous]

Hi Mauro, ok for points 1), 2) and 3). Very well, for point 4) (say 3-bis)) and 5 I understand the spirit, but I have to study the syntax carefully. For point 6) it is not clear to me in which cases it is more flexible than the expressions that manage the bases <16. The 7) very nice,  I like it, but even here I have the doubt if it is preferable to the list.transform which seems clearer to me and is perhaps more efficient because it is simpler.

[amitchandak]

@Anonymous , refer if these two can help

https://community.powerbi.com/t5/Desktop/Convert-mixed-column-Hexadecimal-to-Decimal-in-DAX-or-Power/td-p/360616

https://social.technet.microsoft.com/wiki/contents/articles/33920.power-query-using-recursion-to-solve-hex-to-decimal-conversion.aspx

[Anonymous]

I like this solution (very synthetic) but it is limited to base 16 only

https://community.powerbi.com/t5/Desktop/Convert-mixed-column-Hexadecimal-to-Decimal-in-DAX-or-Power...

the other seem to me a little redundant.