Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Standardize Name fields

    Posted Dec 23, 2021 09:41 PM
    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 =>
    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", "")

    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

  • 2.  RE: Standardize Name fields

    Posted Dec 23, 2021 09:54 PM
    Well, I found one of my errors - I forgot to increment the step names in each line of the M code! Dumb. Anyway, I'm still looking for a more efficient way to write this function. Any help would be super appreciated.

    Chris Mishler