Forum Discussion
Creating New Column Based on a Set of Text Strings
Hi, NewBIEnthusiast
Based on your description, you may click ‘Edit Query’, go to Query Editor, enter ‘Query Settings’, right-click last step, select ‘Insert Step After’.
Then you may input ‘Table.AddColumn(#"Changed Type", "Custom", each if [Color_Name] = "red" or [Color_Name] = "blue" or [Color_Name] = "red;blue" or [Color_Name] = "blue;red" then "Exclude" else "Valid")’ in the formula area.
Result:
As a workaround, you can also create a calculated column as follows.
IsExclude =
IF(
EXACT('Table'[Color_Name],"red")||
EXACT('Table'[Color_Name],"blue")||
EXACT('Table'[Color_Name],"red;blue")||
EXACT('Table'[Color_Name],"blue;red"),
"Exclude",
"Valid"
)
Result:
Best Regards,
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community ChampionHello NewBIEnthusiast
I would always suggest to do this in power query. Here an example how it works
let
ColorName = #table
(
{"Color_Name"},
{
{"red"}, {"orange;yellow"}, {"blue;green"}, {"blue"}, {"red;blue"}, {"yellow;red"}, {"blue;red"}, {"green"}, {"orange;yellow;blue;red"}
}
),
ExclusionList = {"red","blue" },
AddColumnToCheck = Table.AddColumn
(
ColorName,
"Exlcude?",
(add)=> List.Count
(
List.Intersect
(
{
ExclusionList,
Text.Split
(
add[#"Color_Name"],
";"
)
}
)
)>0
)
in
AddColumnToCheck
Copy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy