I have a below text column called "Type" in my data

enter image description here

I wanted to find if column has Annual or Monthly and create new column called "Season". Season column should have Annual, Monthly or None

Expected Output

enter image description here

I am using derived column but not sure how to extract the partial text in the column. Can anyone advise how to do this please?

CodePudding user response：

You can use the following data flow expression in the derived column:

iif( contains(split(Type, ' '), #item == 'Annual'), 'Annual', 
  iif( contains(split(Type, ' '), #item == 'Monthly'), 'Monthly',
  'None'))

Explanation:

• split() functions convert each row in Type column into array. (i.e. the "Founding Annual Member" will be converted to ["Founding Annual Member"] )
• contains() functions return true if the column contains the word "Annual" (first contains function), or "Monthly" (second contains function)
• iif() functions evaluate the result of the contains() functions by returning the word "Annual" (iif in the first row), or "Monthly" (iif in the second row). If no matches found then the second iif() function returns the word "None". (last row)

Result in Data Factory Studio