Forum Discussion
Extract Text after delimiter while igoring repeated words based on Owner ID column
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
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
2 Replies
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
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
