Custom function - for each character within a string

Question

G'day,

I am trying to put a custom formula together, which has very simple logic. Basically, for every string that I pass to it I want it to remove all the non-number characters and apply a leading zero to each digit found (if digit is < 10)

For instance:

function('gi-1/1') should return 0101 (or simply 101 as Integer). Respectively 'gi-1/3' would return 0103 (or 103).

I am struggling to achieve this with mcode and I was hoping if you could help me out. Here is what I managed to put together so far (also, I only want to do this if the number is < 100)

let if_order = (if_name) => 
    let
        Result_1 = Text.Select(if_name, {"0".."9"}) as number,
        Result_2 = if Result_1 < 100 then Text.Combine({Text.Start(Result_1, 1), "0", Text.Middle(Result_1,1)}) else Result_1
    in
        Result_2
in
    if_order

The error I am getting is quite confusing. Basically it's saying:

"We cannot convert the value "11" to type Number"

Hope this makes sense.

Answer 1

The issue is that Text.Select returns text but you're asking it to be a number. You need to do the conversion more explicitly.

Try this modification (outputs the result as text):

let if_order = (if_name) => 
    let
        Result_1 = Text.Select(if_name, {"0".."9"}),
        Result_2 = if Number.FromText(Result_1) < 100
                   then Text.Combine({Text.Start(Result_1, 1), "0", Text.Middle(Result_1,1)})
                   else Result_1
    in
        Result_2
in
    if_order

If you want to return an integer, you could use math instead of string manipulation:

let if_order = (if_name) => 
    let
        Result_1 = Number.FromText(Text.Select(if_name, {"0".."9"})),
        Result_2 = if Result_1 < 100
                   then 100 * Number.IntegerDivide(Result_1, 10)
                            + Number.Mod(Result_1, 10)
                   else Result_1
    in
        Result_2
in
    if_order