Reverse Switch Text by Delimitator

[sslattery17]

A few of our clients send reports with last name, first name and I have used the below formula in the past and I used switch to achieve the same result but can't get it to work.  What part of the syntax am I getting wrong or missing?? Attached sample data and screenshot

with Power Q:

Text.Combine(List.Reverse(Text.Split([Resident],",")), " ")  

Using below and creating new column: 

SWITCH([Resident],",")), " ")

Thank you in advance for the assistance.  SS

[sslattery17]

@CoG I have attached the sample data.  Look at column Occupants; row 395 unit 1001. The resident is Dylan Batt This is the how the occupant data reads; Batt, Jax Batts, Ziggy. 

Ideally I would get a column for Jax Batt and second column for Ziggy Batts.  

[sslattery17]

@CoG @atcodedog05 

Below is the sample data

row 395 unit 1001 resident Batt, Dylan and occupants Batt, Jax Batts, Ziggy

Column Structure

Resident     Occupant 1 Occupant 2

Dylan Batt  Jax Batts       Ziggy Batt

[atcodedog05 · Accepted solution]

Hi @sslattery17 

Here is how you can do it.

Hope this helps : )

[sslattery17]

@atcodedog05 Thank you. 

Now I am trying to figure out the best order to place it in the workflow...There are a lot of other fill down and formulas to get to the final results.  Maybe at the end of the flow?

I will play around with it and see.

[sslattery17]

@CoG and @atcodedog05 

I have used both solutions in different situations are regex & trim work great.  I am still having an issue with columns that have last name, first name separated by a comma and then have two different residents separated by a single comma.  The solutions do reverse or trim in the correct way; except for when I have two individuals.  the syntex still leaves both individuals in the same column.  Is there a way to count if a string has Name 1 , name 2; and move name 2 to a separate column?  If I use text to column; I still have same issue as I bring the two columns back to one if there is only a single name.  See below data sample.

Resident

Gomez Poey, Juan Gabriel

Pelico Perez, Sofia Maibel

Lopez, Richard

Santisteban Arias, Hermes

Palacios, Ana Lucia

Trevino Barajas, Andres (Abraham)