Power Platform Community Forum Thread Details

Hello guys,

I have some data that I would like to manipulate.

Data:

Desired Outcome:

Any help will be highly appreciated.

Categories:

Building flows

Looking at the issue, I recommend using a two-level loop in Power Automate. The first loop processes each record. The second loop splits and expands each person entry.

Step 1. Parse the input JSONAdd a Parse JSON action.Input = your original Data array.

Expected fields

ID
Location
Person

This allows structured access in later steps.

Step 2. Initialize result arrayAdd Initialize variable.

Name: Result
Type: Array
Value: []

This array will store the final flattened output.

Step 3. Loop through each recordAdd Apply to each.

Input: Body from Parse JSON

This loop runs once per ID and Location.

Step 4. Split the Person stringInside the outer loop, add a Compose action.

split(items('Apply_to_each')?['Person'], ';')

This converts multiple people into an array.

Step 5. Loop through each person entry

Add a second Apply to each.

Input: Output of the Compose step

Each iteration now represents one person.

Step 6. Append flattened objectInside the inner loop, add Append to array variable.

Variable name: Result

{
  "ID": items('Apply_to_each')?['ID'],
  "Location": items('Apply_to_each')?['Location'],
  "Role": trim(last(split(split(trim(item()), '|')[0], ':'))),
  "Name": trim(last(split(split(trim(item()), '|')[1], ':'))),
  "Email": trim(last(split(split(trim(item()), '|')[2], ':')))
}

This extracts Role, Name, and Email from each segment and creates one clean record.

Step 7. Handle empty values

If the Person string ends with a semicolon, add a Condition before appending.

Condition expression:

greater(length(trim(item())), 0)

Only append when the value is not empty.

Step 8. Use the final output

Use the Result variable as your final dataset.

You can send it in a response, store it, or generate CSV or JSON output.

You will get one row per person with ID and Location repeated, exactly matching the desired result.

Thank you!

Proud to be a Super User!

📩 Need more help?
✔️ Don’t forget to Accept as Solution if this guidance worked for you.
💛 Your Like motivates me to keep helping

Community site session details

Data Manipulation

Step 1. Parse the input JSONAdd a Parse JSON action.Input = your original Data array.

Step 2. Initialize result arrayAdd Initialize variable.

Step 3. Loop through each recordAdd Apply to each.

Step 4. Split the Person stringInside the outer loop, add a Compose action.

Step 5. Loop through each person entry

Step 6. Append flattened objectInside the inner loop, add Append to array variable.

Step 7. Handle empty values

Step 8. Use the final output

Helpful resources

News and Announcements

Quick Links

Season of Sharing Community Challenge Launch!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to the May Top 10 Community Leaders!

Leaderboard > Power Automate

Featured topics

Product updates

Community site session details

Data Manipulation

Step 1. Parse the input JSONAdd a Parse JSON action.Input = your original Data array.

Step 2. Initialize result arrayAdd Initialize variable.

Step 3. Loop through each recordAdd Apply to each.

Step 4. Split the Person stringInside the outer loop, add a Compose action.

Step 5. Loop through each person entry

Step 6. Append flattened objectInside the inner loop, add Append to array variable.

Step 7. Handle empty values

Step 8. Use the final output

Helpful resources

News and Announcements

Quick Links

Season of Sharing Community Challenge Launch!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to the May Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate

Featured topics

Product updates