Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cleaning strings effectively (power query)

Hi guys,

I have a list of names from two different sources, but they are not exactly the same. Unfortunately, there is no consistent way to split the name based on a delimiter and then pick only the first and surname, because there are inconsistencies in these areas as well.


What are the possibilities in either dax or m to clean data like this?


Example:

Source 1:
Daniel L Jones
Meredith Anne Summer
Chloe Lemaire-Trudeau 
Martin van Hubert

Source 2:
Daniel Jones
Anne Summer
Chloe Lemaire
Martin van der Hubert 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I manage to fix it by doing:

    -A fuzzy merge (join) of the names from the two sources
    -Create ID's (index)
    -Join these to the fact table 

2 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    I studied the examples you provided below and can't find an inherent rule for cleaning. As a workaround, you could use other fields(like unique ids) to match them.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I manage to fix it by doing:

      -A fuzzy merge (join) of the names from the two sources
      -Create ID's (index)
      -Join these to the fact table