Create appended calculated column values based on specific text strings in another column

Hi rchamberlain

This is easier to achieve in PowerQuery. I'll provide a solution for that:


Essentially, you would need to create a list of values that you want to search and extract from:

Next, you would call that list in your main table and build a new custom table from that using the following applied steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktiPcvSsxLT9VRCEhNLNJRAIk4FhTkAAXcixILUpVidSDKwDwdBagcTBNIBqQRrAxNDqoDLg1TC5eBKQTrU4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]),

    // Build a list of strings to search for...
    GetList = List[Value],

    // Let's create a function to extract matching values
    fxExtractValues = 
    
    (txt as text) as text =>
        let
            // Split the original text into a list
            Items = Text.Split(txt, ", "),
            
            // Keep only items with string that starts with value within list of matching values
            MatchingItems = 
                List.Select(
                    Items, each List.AnyTrue(List.Transform(GetList, (p) => Text.StartsWith(_, p)))
                ),
            
            // Remove the prefix from each matching value
            CleanedItems = 
                List.Transform(
                    MatchingItems, each List.First(List.RemoveFirstN(Text.Split(_, "_"), 1))
                ),

            // Combine the results back into a single text string
            Result = Text.Combine(CleanedItems, ", ")
        in
            Result,

    ExtractValues = Table.AddColumn(Source, "Column B", each fxExtractValues([Column A]), type text)
in
    ExtractValues

 
and if done correctly, it should net you the following results:

 

I have included a sample pbix for you to follow along and reference in your actual model.

Hi rchamberlain ,

You can add a custom column in Power Query Editor to get it:

= Text.Combine(
    List.Transform(
        List.Select(
            Text.Split([Column A], ", "), 
            each Text.StartsWith(Text.Lower(_), "dep_")
        ), 
        each Text.AfterDelimiter(_, "_")
    ), 
    ", "
)

The full applied codes as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktiPcvSsxLT9VRCEhNLNJRAIk4FhTkAAXcixILlGJ1IKpAHKAYVAqmByQD0gdWhiYH1QGXhqmFy8AUgvUpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Column B (New)", each Text.Combine(
    List.Transform(
        List.Select(
            Text.Split([Column A], ", "), 
            each Text.StartsWith(Text.Lower(_), "dep_")
        ), 
        each Text.AfterDelimiter(_, "_")
    ), 
    ", "
))
in
    #"Added Custom"

Best Regards