Forum Discussion
Extract 2 digits from a column
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
nullHi 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.
10 Replies
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？？？？？
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 nullHey 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 AzamHi
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 AddColumnTwoDigitsHi 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.
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.
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 endHi 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.
