Although I have been using Power Query for over a year, I haven't figured out an efficient way to clean up name fields in multiple data sets that must be compared. One set might have:
Abigail Smith, Jr.
Fred Coco II
Beulah Mendacious, M.D.
And I want to get to:
Abigail Smith, Jr.
Fred Coco II
Beulah Mendacious, M.D.
One helpful suggestion was to use a custom function in Power Query with M Code as follows:
(myText as text)=> as text =>
let
ToRemove = {""""} & Text.ToList(":[]}"),
CleanText = Text.Remove( myText, ToRemove),
TrimText = Text.TrimEnd(Text.Trim( CleanText ), "."),
TrimText1 = Text.TrimEnd(Text.Trim( TrimText ), ","),
FixSrJr = Text.Replace( TrimText, "Jr", ""),
FixSrJr1 = Text.Replace( FixSrJr, ", Jr", ""),
ReplaceVal1 = Text.Replace(FixSrJr, "Sr", ""),
ReplaceVal2 = Text.Replace(FixSrJr, "M.D.", ""),
ReplaceVal3 = Text.Replace(FixSrJr, "M.D", "")
in
ReplaceVal3
The code worked as far as it goes, but I had trouble when I tried to use Text.Remove in the last line before the "in", as follows:
Trim1= Text.Remove(ReplaceVal3, {" II", " III"," IV"})
Which threw off an error that the values were not single characters.
How can I give the function a list of characters to remove, or replace with null or ""?
By the way, I would invoke Custom Function to run the function against a particular column in one data set, then run the same in the comparison data set because I may do a merge based on these name fields and they need to be formatted the same and spelling differences eliminated, etc.
Thanks. First time poster here.
------------------------------
Chris Mishler
------------------------------