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