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