Forum Discussion

rchamberlain's avatar
rchamberlain
Frequent Visitor
1 year ago
Solved

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

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 (...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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