Forum Discussion
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 (...
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
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
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.