Extracting complicated text from a column

Hi afaherty 
Thank you for reaching out to the Microsoft Fabric community forum.

You can do this natively in Power Query without Python by splitting the list into tokens, trimming, then filtering only the items that start with INTASC/InTASC (case-insensitive). This also handles rows with multiple INTASC values and returns them joined with |.

Add a custom column with:
 

each
    let
        t = [Current Data],
        cleaned0 =
            if t = null or Text.Trim(t) = ""
            then null
            else t,
        cleaned =
            if cleaned0 = null
            then null
            else Text.Replace(
                    Text.Replace(
                        Text.Replace(
                            Text.Replace(cleaned0, "[", ""),
                        "]", ""),
                    "(", ""),
                ")", ""),
        parts =
            if cleaned = null
            then {}
            else List.Transform(
                    Splitter.SplitTextByAnyDelimiter({",", ";"}, QuoteStyle.Csv)(cleaned),
                    each Text.Trim(_)
                 ),
        intascOnly =
            List.Select(
                parts,
                each Text.StartsWith(Text.Trim(_), "INTASC", Comparer.OrdinalIgnoreCase)
            ),
        result =
            if List.Count(intascOnly) = 0
            then null
            else Text.Combine(intascOnly, " | ")
    in
        result

This will return InTASC 10m | INTASC 61s when multiple values exist, or blank/null if none are present.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Regards,

Microsoft Fabric Community Support Team

Hi afaherty 

Since the text can contain words before the brackets, the correct approach is to first extract only the content inside [ ... ], then split and filter for InTASC.

Add a custom column with.

each
    let
        t = [Current Data],

        insideBrackets =
            if t = null
            then null
            else try Text.BetweenDelimiters(t, "[", "]") otherwise null,

        parts =
            if insideBrackets = null
            then {}
            else List.Transform(
                    Splitter.SplitTextByAnyDelimiter({",", ";"}, QuoteStyle.Csv)(insideBrackets),
                    each Text.Trim(Text.Clean(_))
                 ),

        intascOnly =
            List.Select(
                parts,
                each Text.StartsWith(_, "INTASC", Comparer.OrdinalIgnoreCase)
            ),

        result =
            if List.Count(intascOnly) = 0
            then null
            else Text.Combine(intascOnly, " | ")

    in
        result

This will correctly return InTASC 3n | InTASC 8c for multiple matches, InTASC 5b for a single match, or null if no match is found.

If you have any other questions, feel free to contact us. We're here to help.

Regards,

Microsoft Fabric Community Support Team.