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

[rchamberlain]

Hello -

I am seeking help on creating an appended calculated column (Column B) using data from another column's (Column A) values.

I need a way to search a column for a specific text string (I.e. "Dep_" in below example) and add what follows it in the new column.  However, in many cases there could be more than one instance of that string value.  If this happens, I would like to seperate them by comma's as noted below.

Also, it needs to work independent of case (I.e. "Dep_" or "dep_").

Example

Column A	Column B (New)
Dep_Orange, Pear, Dep_Apple, Grap	Orange, Apple
Dep_Grape, Apple, Orange, Dep_Pear	Grape, Pear
Apple, Orange, Grape, Pear
Dep_Pear, Grape, Orange, Apple	Pear

This may be super easy for you super users so any help is appreciated!

Thanks,

Rich

[Anonymous · Accepted solution]

Hi @rchamberlain ,

You can achieve it by creating a calculated column:

Column B = 
VAR TextString = "Dep_"
VAR ColumnAValues =
    SUBSTITUTE ( [Column A], TextString, TextString )
VAR ExtractedValues =
    CONCATENATEX (
        FILTER (
            ADDCOLUMNS (
                GENERATESERIES ( 1, LEN ( ColumnAValues ) - LEN ( TextString ) + 1 ),
                "Substring", MID ( ColumnAValues, [Value], LEN ( TextString ) ),
                "FollowingText",
                    MID (
                        ColumnAValues,
                        [Value] + LEN ( TextString ),
                        FIND ( ",", ColumnAValues & ",", [Value] + LEN ( TextString ) )
                            - ( [Value] + LEN ( TextString ) )
                    )
            ),
            [Substring] = TextString
        ),
        [FollowingText],
        ", "
    )
RETURN
    IF ( ExtractedValues = "", BLANK (), ExtractedValues )

Best Regards

[rchamberlain]

Thank you!  This works exactly as I need.

I'm not as familiar with Powerquery at this time, but I am sure the other solutions would have worked as well.

[Anonymous]

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

[hnguy71]

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.