Extract 2 digits from a column

[santoshfx]

I have added a custom column to extract the 2 digits from the Summary field for the records that begins with "A2B" . I have tested this in advanced editor and the output is expected. However, when I created a table and dragged, the summary and the new column I see discrepancy for one of the value.

A2B_7b output is showing as 74...why?

Sample Summary values

A2B 11:Test

A2B_23

A2B-92

A2B_02,03ABC

A2B34

A2B_7b

if Text.Upper(Text.Start([Summary], 3)) = "A2B" then
    let
        digits = Text.Select(Text.Middle([Summary], 3), {"0".."9"}),
        digitCount = Text.Length(digits)
    in
        if digitCount >= 2 then Text.Start(digits, 2) else null
else
    null

[v-priyankata]

Hi @santoshfx 
@wdx223_Daniel @Nasif_Azam @mromain @Direct Thank you so much for your inputs.

I hope the information shared was helpful to you. If your question has been answered, kindly mark the most relevant reply as the Accepted Solution. This small action can make a big difference for others who are looking for the same solution.

[mromain]

Hi

Here a possible solution:

let
    Source = #table(
        type table [Summary = text],
        {
            {"A2B 11:Test"},
            {"A2B_23"},
            {"A2B-92"},
            {"A2B_02,03ABC"},
            {"A2B34"},
            {"A2B_7b"},
            {"A2B_Smoke Int 12"},
            {"A2B_Smoke Int 14"},
            {"A2B_7B-INT4243"}
        }),
    AddColumnTwoDigits = 
        let
            fnExtractDigits = (t as text, startsWith as text) as nullable text =>
                let
                    fnRemoveTexts = (t as text, texts as list) as text => List.Accumulate(texts, t, (s, c) => Text.Replace(s, c, "")),
                    ExtractCars = if Text.StartsWith(t, startsWith, Comparer.OrdinalIgnoreCase) then Text.ToList(Text.Middle(fnRemoveTexts(t, {" ", "-", "_"}), Text.Length(startsWith), 2)) else {},
                    SelectDigits = List.Select(ExtractCars, each List.Contains({"0" .. "9"}, _))
                in
                    if List.Count(SelectDigits) = 2 then Text.Combine(SelectDigits, "") else null
        in
            Table.AddColumn(Source, "Digits", each fnExtractDigits([Summary], "A2B"), type text)
in
    AddColumnTwoDigits

[Nasif_Azam]

Hey @santoshfx ,

Please try the Power Query M code:

if Text.Upper(Text.Start([Summary], 3)) = "A2B" then
    let
        // Remove "A2B" prefix and trim
        afterPrefix = Text.Trim(Text.Middle([Summary], 3)),

        // Use a regular expression-like trick to extract only when number comes immediately
        matches = List.Transform(
            Text.SplitAny(afterPrefix, " _-:"), 
            each Text.Select(_, {"0".."9"})
        ),

        firstValid = List.First(List.Select(matches, each Text.Length(_) = 2), null)
    in
        firstValid
else
    null

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Direct]

please try this one, modified a bit

if Text.Upper(Text.Start([Summary], 3)) = "A2B" then
    let
        textToSearch = Text.Middle([Summary], 3, 10),  // Scan only the next 10 characters
        length = Text.Length(textToSearch),
        windows = List.Transform({0..length - 2}, each Text.Middle(textToSearch, _, 2)),
        digitPairs = List.Select(windows, each Text.Length(_) = 2 and Text.Remove(_, {"0".."9"}) = "")
    in
        if List.Count(digitPairs) > 0 then digitPairs{0} else null
else
    null

[wdx223_Daniel]

if Text.Upper(Text.Start([Summary], 3)) = "A2B" then
    let
        digits = List.Transform({3..Text.Length([Summary])-2},(x)=>Text.Middle([Summary],x,2))
    in
        List.Select(digits,each Text.Remove(_,{"0".."9"})=""){0}?
else
    null

[santoshfx]

Thanks for the quick response

while it works for majority of records, still few records not working as expected

A2B_Smoke Int 12 Output is 12 instead of null
A2B_Smoke Int 14 Output is 14 instead of null
A2B_7B-INT4243 Output is 42 instead of null

[wdx223_Daniel]

？？？？？