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

[Poojara_D12 · Accepted solution]

Hi @santoshfx 

You're trying to extract the first two digits from a Summary field in Power Query for values that start with "A2B", and while your custom column works correctly for most cases, you're seeing an unexpected result for the value "A2B_7b", which returns "74" instead of the expected "07" or null. Your logic uses Text.Middle([Summary], 3) to skip the "A2B" prefix, and then Text.Select(..., {"0".."9"}) to isolate digits. In theory, "A2B_7b" should yield just "7", and since there's only one digit, the logic should return null. However, the result of "74" suggests that the string may contain unexpected or hidden characters, or you might be referencing a different variation of the record (like "A2B_74b") without realizing it. This discrepancy can occur due to invisible formatting, trailing digits, or earlier query steps modifying the data. To identify the issue, you can create a temporary column showing the output of Text.Middle([Summary], 3) or use Text.ToList(...) to break the string into individual characters for closer inspection. This will help confirm what text is being evaluated and reveal whether the unexpected "4" is actually present in the string.

[Omid_Motamedise]

Hi @santoshfx 

Lets apply your formula to: A2B_7b

Text.Middle("A2B_7b", 3) → returns: "_7b"

Text.Select("_7b", {"0".."9"}) → returns: "7"

digitCount = 1 → does not meet >= 2, so result = null

So this should not return "74" under this logic.

as a result, you might have a different Summary value than you think including white spacee at the end 

[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.

[v-priyankata]

Hi @santoshfx 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[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]

？？？？？