Creating New Column Based on a Set of Text Strings

[NewBIEnthusiast]

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!

[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

[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...

[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

[Jimmy801]

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
	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

[v-alq-msft · Accepted solution]

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.

[amitchandak]

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-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601