Forum Discussion
Extract 2 digits from a column
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.
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
nullIf 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