Forum Discussion
Delimeters - Data Cleansing/Best Practices
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?
1 Reply
- LivioLanzo
Solution Sage
Hi Anonymous
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
