Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Nusc
Helper II
Helper II

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 1
LivioLanzo
Solution Sage
Solution Sage

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.