Split data into 2 columns in SPO using PowerShell PnP and use Power Automate for new items

Woow, that title was pretty long! But we are going to use SharePoint Online, PowerShell PnP, and also Power Automate (aka Microsoft Flow) so you really needed to know that we’re about to built here 😅

If you prefer to watch a video, I’ve made one on my YouTube channel, but that was before figuring out the flow part… So this blog post is for completing the ‘idea’ of automating the whole thing.

The idea

The idea here is to split the data from one column into 2 other separate columns in SharePoint Online.
An easy example would be a column being called “Full Name“. We’d want to split this data in a column named “First Name“, and another one called “Last Name“.

The script

Let’s have a look at the script. As usual, we connect to SharePoint Online using the Connect-PnPOnline cmdlet.
Then, we target the list containing our column(s) —> called “Clients” in our example.

#Connect to SPO
Connect-PnPOnline -Url https://<TENANT-NAME>.sharepoint.com/sites/<SITE>

#Store my list into a variable
$myList = Get-PnPList -Identity "Clients"

The next part is the meat and potatoes 😉
Let me explain first what we do, and break things down:

1. We create an array to store the results
2. We fetch the data from FullName, FirstName, and LastName columns using the Get-PnPListItem cmdlet, and store them into a variable called $allItems
3. We create a foreach loop where we…
   • Split the FullName value when there’s a space — hence .Split(" ")
   • Store the 1st object of the Full Name (i.e.: John) into a variable called $firstNameSplit
   • Store the 2nd object of the Full Name (i.e. Smith) into a variable called $lastNameSplit
4. We create our custom object
5. We use the Set-PnPListItem and set the values for our First Name & Last Name columns

ℹ️ Everytime you see “FullName”, “FirstName”, “LastName” between quotes, those are the columns internal names. So yours may differ.

Get all items from the list + Store the results
$results = @()

$allItems = Get-PnPListItem -List $myList -Fields "FullName", "FirstName", "LastName"

foreach ($item in $allItems) {
    $splitFullName = $item["FullName"].Split(" ")
    $FirstNameSplit = $splitFullName[0]
    $LastNameSplit = $splitFullName[1]
    
    $results += [pscustomobject][ordered]@{
        FullName  = $item["FullName"]
        FirstName = $item["FirstName"]
        LastName  = $item["LastName"]
    }

    #Modify each current item in the list
    Set-PnPListItem -List "Clients" -Identity $item -Values @{"FirstName" = $FirstNameSplit; "LastName" = $LastNameSplit }
}
$results

The Flow

If you watched the video on my Youtube channel, you’re missing the flow part. So here it is!

Go into Power Automate (aka Microsoft Flow), and start with an Automated flow.

1. The trigger will be in the SharePoint connector “When an item is created“. Enter your site URL, as well as your list (i.e.: Clients in our case)
2. Get the items (SharePoint connector)
3. Add an “Apply to each” action with value from the previous step, and in this “Apply to each” —> let’s “Update the item“

For the First Name & Last Name column, we’ll use a function as follow (similar to the PowerShell script 😉):

• First Name —> split(triggerBody()?['FullName'],' ')[0]
• Last Name —> split(triggerBody()?['FullName'],' ')[1]

That’s it! Only one thing left to do… Test it out!