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:
- We create an array to store the results
- We fetch the data from FullName, FirstName, and LastName columns using the
Get-PnPListItem
cmdlet, and store them into a variable called$allItems
- 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
- Split the FullName value when there’s a space — hence
- We create our custom object
- 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.
- 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)
- Get the items (SharePoint connector)
- 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!