Extract Text after delimiter while igoring repeated words based on Owner ID column

[lc881]

I have data like the table below:

Owner ID	Pet Description	[Desired Output]
123	Dog Light Brown	Light Brown
123	Dog Dark shade of Green	Dark shade of Green
123	Dog Black	Black
456	House Cat Purple	Purple
456	House Cat Light Blue	Light Blue
789	Mouse White	White

I need to create a new column that will contain just the Colour of the Pet, e.g. Light Brown, Dark shade of green etc.
However, I can't figure out how to Extract the Colour info as the description field is not consistent.
I planned to do Extract Text after Delimiter " ", but because of the inconsistencies it will not work.
Is there a way (in DAX, Power Query, anything) where I can recreate the following logic:
For each row of Owner ID XXX e.g. 123, Ignore words that are found in all the rows e.g. 'Dog' in the first 3 rows, and extract the text afterwards.
NOTE: In my dataset, there will NOT be duplicate description values ie. all unique

[davehus · Accepted solution]

Hi @lc881 ,

In power query, create a custom column and use the formula below.

if Text.Contains([Pet Description], "Dog") then Text.Replace([Pet Description],"Dog","") else if Text.Contains([Pet Description], "House Cat") then Text.Replace([Pet Description],"House Cat","") else ""

Then trim and clean the column. You can build it up as desired with your pet name variations.

HTH

[lc881]

Doing it in Power Query was the advice I needed, thanks.
As there were hundreds and hundreds of rows with many different pet descriptions, it would've taken too long to manually write a long if statement with all the names etc. (Should've specified that in the question)

What I had to do was create a column that counted the number of words in Pet Description. I then Grouped the Owner ID by MIN of Word Count. I then added a custom column which computed the Word Count of that row - MIN for that Owner ID. I then used this value as a variable in a 'Split by delimiter' function. Not pretty but probably the best outcome for me.

Because your solution answers the scope of the question I posted and would be helpful for beginners, I am going to mark it as solution 🙂 thanks