Reverse name and last name but with a constraint

[Anonymous]

I want to reverse the first and last name in a column but there are sentences in that same column. I don't want to touch the sentence but recover them as they are. (If possible in M) Thank you 

[PijushRoy · Accepted solution]

Hi @Anonymous 

You can try these power query steps
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Agents", "Agents - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Agents - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Agents - Copy.1", "Agents - Copy.2", "Agents - Copy.3", "Agents - Copy.4"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Agents - Copy.1", type text}, {"Agents - Copy.2", type text}, {"Agents - Copy.3", type text}, {"Agents - Copy.4", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Custom", each if [#"Agents - Copy.4"] = null then 1 else 0),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Final Column", each if [Custom] = 1 then ([#"Agents - Copy.2"]&" "&[#"Agents - Copy.1"]) else [Agents]),
#"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Agents - Copy.2", Text.Trim, type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Agents - Copy.1", "Agents - Copy.2", "Agents - Copy.3", "Agents - Copy.4", "Custom"})
in
#"Removed Columns"

If you have any question, please let me know.
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

[aswanth]

Hi Piyush,

If I need to swap the first and last names in the cell and also while swapping if I need to have the final format like last name ,First name in caps. (Lastname space comma first letter of first name in caps and end with a period) do we have the option to make the transform query changed in the same way. Now this is part of the requirement where i will have manys excels and then I need to load them and change few columns according to the required values like extracting first two chars of a column, for one of the columns after extraction converting them into upper case. and the above one for first and last name in the same sheet

[Anonymous]

Thank you, this answers a lot. There is one more point. How to manage people with several first and last names but I think we can't really calculate it. Except with an additional indicator in a pre-existing column that would indicate that in a specific case it is a compound name.