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.
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
nullThanks 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？？？？？
