Delete middle initial from name

Question

I have a Power Query/M question. I have a list of full names, some of which have middle initials (without a period) and some of which do not. Names (and the possible initial) are delimited by spaces. Some of the names have multiple first or last names which I would want to keep. So you might have a mix of names like

John Smith
John Q Smith
Maggie Q Smith
Anne Margaret Smith
Ann Margaret Q Smith
Anne Margaret Nixon Smith

I would like to return all the names but not the initials. In other languages I would use regex but I cannot do so in PQ, and I'm just beginning to learn M. Has anyone come across something similar?

Answer 1

@ClearScreen . You're welcome & Thanks for posting back
If that did it, the following does the same with List.Accumulate

let  
    Source = Table.FromList(  
        {"John Smith","John Q Smith","Maggie Q Smith","Anne Margaret Smith",  
         "Anne Margaret Q Smith","Anne Margaret Y Nixon Smith"}, null,  
        type table [Full Name = text]  
    ),  
    RemovedInitial = Table.AddColumn(Source, "Clean Full Name", each  
        List.Accumulate(Text.Split([Full Name], " "), null,  
            (state,current)=> if Text.Length(current) > 1  
                then Text.Combine({state, current}, " ") else state  
        ),  
        type text  
    ),  
    RemovedColumn = Table.SelectColumns(RemovedInitial,{"Clean Full Name"}),  
    RenamedColumn = Table.RenameColumns(RemovedColumn,{<!-- -->{"Clean Full Name", "Full Name"}})  
in  
    RenamedColumn  

A couple of articles re. List.Accumulate:

• Power Query List.Accumulate – Unleashed
• BIN2DEC in Power Query: Implementations of List.Accumulate. Part 1

Answer 2

@ClearScreen

OK, so the following does it assuming you don't have a Full Name like "A John Smith" where A isn't in the middle but will be remove anyway. If you need to keep it as "A John Smith" in this case let me know.

As you begin with PQ I decomposed the steps with (hopefully) clear step names inside Table.AddColumn

let  
// Table for demo.  
    Source = Table.FromList(  
        {"John Smith","John Q Smith","Maggie Q Smith","Anne Margaret Smith",  
         "Anne Margaret Q Smith","Anne Margaret Y Nixon Smith"}, null,  
        type table [Full Name = text]  
    ),  
//  
    RemovedInitial = Table.AddColumn(Source, "Clean Full Name", each  
        let  
            SplittedByDelim = Text.Split([Full Name], " "),  
            SelectedByLength = List.Select(SplittedByDelim, each Text.Length(_) > 1),  
            CombinedItems = Text.Combine(SelectedByLength, " ")  
        in  
            CombinedItems,  
            type text  
    ),  
    RemovedColumn = Table.SelectColumns(RemovedInitial,{"Clean Full Name"}),  
    RenamedColumns = Table.RenameColumns(RemovedColumn,{{"Clean Full Name", "Full Name"}})  
in  
    RenamedColumns  

Any question let me know

Answer 3

@ClearScreen . To shorten your 1st function

(FullName as text) as text =>  
let  
    SplittedByDelim = Text.Split(FullName, " ")  
in  
    List.Accumulate(SplittedByDelim, null,  
        (state, current) => if Text.Length(current) > 1  
            then Text.Combine({state, current}, " ")  
            else state  
    )  

OR, even shorter

(FullName as text) as text =>  
    List.Accumulate(Text.Split(FullName, " "), null,  
        (state, current) => if Text.Length(current) > 1  
            then Text.Combine({state, current}, " ")  
            else state  
    )