Forum Discussion
Creating New Column Based on a Set of Text Strings
Hello,
I am a fairly new user to PowerBI and I am trying to solve a problem. I have a data set with a column called "Color_Names." This column can contain anywhere from 0 - 5 colors within the field and it is delimited by a semi-colon. Example below:
| Color_Name |
| red |
| orange;yellow |
| blue;green |
| blue |
| red;blue |
yellow;red |
blue;red |
green |
|
orange;yellow;blue;red |
I then have a color "exclusion" list, trying to keep things simple here. Based on that exclusion list (which is not stored anywhere), I want to create a new column that indicates whether my record should be excluded based on that list. The color exclusion list, in my example would be blue and red. If any of my fields have only red or blue OR both blue and red, it should show the text "Exclude," otherwise, show the text "Valid." I want the new column to look like this (for example.)
| Color_Name | Exclude? |
| red | Exclude |
| orange;yellow | Valid |
| blue;green | Valid |
| blue | Exclude |
| red;blue | Exclude |
yellow;red | Valid |
blue;red | Exclude |
green | Valid |
|
|
orange;yellow;blue;red | Valid |
How can I calcuate or determine this? I've tried using text.split to move my values into a list, and I can even use the List.ContainsAny function to throw back true or false on whether the list contains my text, but I need to get one level further. I even tried figuring out how to just count the number of items in the list that appear on exclusion and compare to total count of list, but I can't get the exclusion list count nailed down.
Any suggestions are very appreciated!
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.
6 Replies
- amitchandak
Super User
Use search
https://docs.microsoft.com/en-us/dax/search-function-dax
if(search("Red",color_name)>0 &&search("Blue",color_name)>0 , "Exclude","Include")Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601 
v-alq-msftCommunity Support
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.
Jimmy801Community Champion
Hello 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 AddColumnToCheckCopy 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- Jimmy801
Hello NewBIEnthusiast
have you been able to solve the problem with the replies given?
If so, 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
All the best
Jimmy - littlemojopuppy
How you have your data modeled completely violates first normal form, and as a result you are likely complicating this far more than it otherwise would be with a well-formed data model...
- v-alq-msft
Hi, NewBIEnthusiast
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
