Delimeters - Data Cleansing/Best Practices

[Nusc]

Hi,

so there were data entries in this column that were separated in the following form:

pigs - type - cats

but some entries have

pigs - types - cats

pig-s - type - cats

pigs - type - cats -oct-11-2017

So unfortunately I cannot separate this column by delimiter.

What is the best way of doing this? Do you recommend an if statement that checks to see if there is a '-' more than 3 times ?

I would also need to write something that checks for the first several letters to normalize 'type' and 'types'.

What is the name of this function?

[LivioLanzo]

Hi @Nusc

You can get the text before the 3rd delimiter with this function: https://docs.microsoft.com/en-us/powerquery-m/text-beforedelimiter

You could add an extra - at the end of each string before calling this function

For the other problem, probablt this function can help you: https://docs.microsoft.com/en-us/powerquery-m/text-replace