Forum Discussion
Removing non-forename/surname data from various columns?
Greetings, esteemed Power Query gurus!
🙂
I'm hoping someone can help me with this issue, as I am a newbie.
I am attempting to set up a Power Query so that whenever an updated version of raw data is linked, it will run through the steps I've set-up and remove everything I do not need.
I have done a few things already (such as splitting columns and delimiting after the space), but I'm struggling to find the best way to remove non-forename and surname data.
Example of problem:
I would dearly love to know how to remove a specific list of words that will continually appear in the raw data I get, such as "PHONE", "BOOKING", "&", etc...
(Basically, ANY and ALL non-names.)
Another issue is that some people's names are hyphentaned, or have "Van" or "Mc" in them, which may cause issue as per the fictional person above, Chris Van Durban.
Is there a way to create a list of words that PowerQuery will remove each and everytime, as the data I'll recieve will have this sort of thing in it everytime.
I hope this makes sense!
Thank you very much in advance for any tips...and there's a lollipop in it for the person who solves my issue!
😉
Benochian
3 Replies
- KT_Bsmart2gethe
Impactful Individual
Hi Anonymous ,
From your screenshot, I assumed you had applied a split column function.
The simple way to remove those non-forename/surnames is to apply another step beforehand.
For example, use to replace value function to replace "PHONE" and "BOOKING & ASSETS" to ";PHONE" and ";" BOOKING & ASSETS". Then, apply split column by delimiter ";" before you split the name.
Of course, there are dynamic ways to achieve it, and I need sample data to write you the M code to prevent errors and back-and-forth communication.
Regards
KT
Ah, thank you!
That would make sense.
🙂
I'll defintely give it a go and come back to you KT_Bsmart2gethe
