Split Column to New Columns

0 Views

Like (0)

Report

Posted 04 Nov 2024 by takolota1

4,851 Super User 2025 Season 1

Have you ever needed to split on a column to create multiple new columns like you would in Power Query / Power BI?
Try this efficient method that takes roughly 2 actions per new column.

If you need to split a column to new rows instead of new columns, see this post.

Just enter your split Column name, your split Delimiter, & your JSON array of data with your split Column in the SplitSettings action.

And you can turn something like this...

With a little processing...

... into this

Thanks for any feedback,

Please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86).

And reach out on LinkedIn (https://www.linkedin.com/in/kolota/) if you want to hire me to consult or build more custom Microsoft solutions for you.

Solution Zip Download Link: https://drive.google.com/file/d/1M67vhFbsxdYfpJpzBlKpI80nUi_qUPUK/view?usp=sharing

Legacy Power Automate Import: https://drive.google.com/file/d/1pO_4-ckukn-WcfRwchKh2GX-QR8VRT03/view?usp=sharing

If you want to use the scope copy & paste method below, you will need to Initialize variable for an array variable called Data.

Scope code to copy & paste into your flow on the New Designer:

{"nodeId":"Split_Column_Sample-copy","serializedOperation":{"type":"Scope","actions":{"SampleData":{"type":"Compose","inputs":[{"Created":"01-01-2025","TestColumn":"Inv10002|Inv10003"},{"Created":"02-10-2025","TestColumn":"Inv10004|Inv10005|Inv10006"},{"Created":"03-20-2025","TestColumn":"Inv10007"},{"Created":"04-28-2025","TestColumn":null},{"Created":"05-05-2025","TestColumn":""}],"metadata":{"operationMetadataId":"cbd25700-ee7f-4978-af0d-2ab61bc44f59"}},"Split_Column":{"type":"Scope","actions":{"SplitSettings":{"type":"Compose","description":"Enter the column name to split on, the delimiter in the column values to split on, and the JSON array containing the data & column to split","inputs":{"Column":"TestColumn","Delimiter":"|","Data":"@outputs('SampleData')"},"metadata":{"operationMetadataId":"b4fa35f3-8585-4ad2-985c-7ef837f3e624"}},"Select_Split_lengths":{"type":"Select","inputs":{"from":"@variables('Data')","select":"@if(empty(item()[outputs('SplitSettings')['Column']]), 0, length(split(item()[outputs('SplitSettings')['Column']], outputs('SplitSettings')['Delimiter'])))"},"runAfter":{"Set_variable_Data":["Succeeded"]},"metadata":{"operationMetadataId":"ec3a16d9-1fa2-409b-9b3e-d8f72b94eb0b"}},"Apply_to_each":{"type":"Foreach","foreach":"@range(0, last(sort(body('Select_Split_lengths'))))","actions":{"Select_Add_next_column":{"type":"Select","inputs":{"from":"@variables('Data')","select":"@addProperty(item(), concat(outputs('SplitSettings')['Column'], ' ', add(1, items('Apply_to_each'))), if(empty(item()[outputs('SplitSettings')['Column']]), null, split(item()[outputs('SplitSettings')['Column']],outputs('SplitSettings')['Delimiter'])?[items('Apply_to_each')]))"},"metadata":{"operationMetadataId":"a0c57392-a9ea-42fc-8927-3470ef2e83ee"}},"Set_variable_Data_2":{"type":"SetVariable","inputs":{"name":"Data","value":"@body('Select_Add_next_column')"},"runAfter":{"Select_Add_next_column":["Succeeded"]},"metadata":{"operationMetadataId":"3d3314c7-8121-44d7-a547-f66cd0bedacd"}}},"runAfter":{"Select_Split_lengths":["Succeeded"]},"metadata":{"operationMetadataId":"472e7fb5-d015-45e4-bd20-1619983a617c"}},"Set_variable_Data":{"type":"SetVariable","inputs":{"name":"Data","value":"@outputs('SplitSettings')?['Data']"},"runAfter":{"SplitSettings":["Succeeded"]},"metadata":{"operationMetadataId":"c1637373-c421-4814-b43f-397b2f0064cb"}},"Output":{"type":"Compose","inputs":"@variables('Data')","runAfter":{"Apply_to_each":["Succeeded"]},"metadata":{"operationMetadataId":"cc6f6b00-c666-41ee-a248-5c57e58d3589"}}},"runAfter":{"SampleData":["Succeeded"]},"metadata":{"operationMetadataId":"9cd63a68-5fb8-4f54-8ab3-035b8ed24a2a"}}},"runAfter":{"Initialize_variable_Data":["Succeeded"]},"metadata":{"operationMetadataId":"9f875f30-1e49-464e-81cd-bf74d48c6b7b"}},"allConnectionData":{},"staticResults":{},"isScopeNode":true,"mslaNode":true}

Scope code to copy & paste into your flow on the Classic Designer:

{"id":"f392a431-6bce-4d03-b0ff-0e4c86df8eba","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Split_Column_Sample","operationDefinition":{"type":"Scope","actions":{"SampleData":{"type":"Compose","inputs":[{"Created":"01-01-2025","TestColumn":"Inv10002|Inv10003"},{"Created":"02-10-2025","TestColumn":"Inv10004|Inv10005|Inv10006"},{"Created":"03-20-2025","TestColumn":"Inv10007"},{"Created":"04-28-2025","TestColumn":null},{"Created":"05-05-2025","TestColumn":""}],"runAfter":{},"metadata":{"operationMetadataId":"cbd25700-ee7f-4978-af0d-2ab61bc44f59"}},"Split_Column":{"type":"Scope","actions":{"SplitSettings":{"type":"Compose","inputs":{"Column":"TestColumn","Delimiter":"|","Data":"@outputs('SampleData')"},"runAfter":{},"description":"Enter the column name to split on, the delimiter in the column values to split on, and the JSON array containing the data & column to split","metadata":{"operationMetadataId":"b4fa35f3-8585-4ad2-985c-7ef837f3e624"}},"Select_Split_lengths":{"type":"Select","inputs":{"from":"@variables('Data')","select":"@if(empty(item()[outputs('SplitSettings')['Column']]), 0, length(split(item()[outputs('SplitSettings')['Column']], outputs('SplitSettings')['Delimiter'])))"},"runAfter":{"Set_variable_Data":["Succeeded"]},"metadata":{"operationMetadataId":"ec3a16d9-1fa2-409b-9b3e-d8f72b94eb0b"}},"Apply_to_each":{"type":"Foreach","foreach":"@range(0, last(sort(body('Select_Split_lengths'))))","actions":{"Select_Add_next_column":{"type":"Select","inputs":{"from":"@variables('Data')","select":"@addProperty(item(), concat(outputs('SplitSettings')['Column'], ' ', add(1, items('Apply_to_each'))), if(empty(item()[outputs('SplitSettings')['Column']]), null, split(item()[outputs('SplitSettings')['Column']],outputs('SplitSettings')['Delimiter'])?[items('Apply_to_each')]))"},"runAfter":{},"metadata":{"operationMetadataId":"a0c57392-a9ea-42fc-8927-3470ef2e83ee"}},"Set_variable_Data_2":{"type":"SetVariable","inputs":{"name":"Data","value":"@body('Select_Add_next_column')"},"runAfter":{"Select_Add_next_column":["Succeeded"]},"metadata":{"operationMetadataId":"3d3314c7-8121-44d7-a547-f66cd0bedacd"}}},"runAfter":{"Select_Split_lengths":["Succeeded"]},"metadata":{"operationMetadataId":"472e7fb5-d015-45e4-bd20-1619983a617c"}},"Set_variable_Data":{"type":"SetVariable","inputs":{"name":"Data","value":"@outputs('SplitSettings')?['Data']"},"runAfter":{"SplitSettings":["Succeeded"]},"metadata":{"operationMetadataId":"c1637373-c421-4814-b43f-397b2f0064cb"}},"Output":{"type":"Compose","inputs":"@variables('Data')","runAfter":{"Apply_to_each":["Succeeded"]},"metadata":{"operationMetadataId":"cc6f6b00-c666-41ee-a248-5c57e58d3589"}}},"runAfter":{"SampleData":["Succeeded"]},"metadata":{"operationMetadataId":"9cd63a68-5fb8-4f54-8ab3-035b8ed24a2a"}}},"runAfter":{"Initialize_variable_Data":["Succeeded"]}}}

code snippet widget

Categories:

AI Builder

Comments

takolota1 4,851 Super User 2025 Season 1
Posted 11 Nov 2024 at 20:52:06

Split Column to New Columns

Description

Placeholder

Like (0)

Report

Community site session details

Split Column to New Columns

Categories:

Comments