Build Array from SharePoint List

(0) Share

Report

Posted on by CU27091846-0

Hello,

I have been trying to build an array from a SharePoint list, data looks like this:

Desired outcome in Power Automate:

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (2)

Sort by

Verified answer

Kalathiya 2,456 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @CU27091846-0,

Please refer below steps:

#1. Get items (SharePoint)

#2. Initialize "AuditorArray" Array type variable.

#3. Add an “Apply to Each” action and select the Value property from the Get Items action.

#4. Add a condition for each auditor:

The auditor’s email is not null

5. In the “If true” branch, add an “Append to array” action and include all the relevant details.
6. Repeat steps 4 and 5 for all audit columns.

Note: Role value (Auditor 1, Auditor 2, etc.) must be entered manually in each “Append to array variable” action.

---------------------------------------------------------------------------------

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

Was this reply helpful? Yes No
Verified answer

Chriddle 8,708 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
This flow takes your list (mocked in Compose-ListData) and a dictionary (to get the e-mail addresses).

A Select creates for each list item an array of up to 5 objects with the wanted properties.

A second Select, flattens the returned array.

Compose-ListData

[ { "ID": 5020, "Location": "Washiongton", "Auditor1": "Isabella", "Auditor2": "", "Auditor3": "Ana", "Auditor4": "", "Auditor5": "" }, { "ID": 4040, "Location": "Mexico", "Auditor1": "Luis P", "Auditor2": "", "Auditor3": "", "Auditor4": "", "Auditor5": "Pedro A" } ]

Compose-Auditors

{ "Ana": "ana@example.com", "Isabella": "isabella@example.com", "Luis P": "luisp@example.com", "Pedro A": "pedroa@example.com" }

Select

From

outputs('Compose-ListData')

Map

json( concat( '[', if( empty(item().Auditor1), '', concat( '{', '"ID": ', item().ID, ',', '"Location": "', item().Location, '",', '"Role": "Auditor1",', '"AuditorName": "', item().Auditor1, '",', '"AuditorEmail": "', outputs('Compose-Auditors')[item().Auditor1], '"', '}' ) ), if( empty(item().Auditor2), '', concat( if(empty(item().Auditor1), '', ','), '{', '"ID": ', item().ID, ',', '"Location": "', item().Location, '",', '"Role": "Auditor2",', '"AuditorName": "', item().Auditor2, '",', '"AuditorEmail": "', outputs('Compose-Auditors')[item().Auditor2], '"', '}' ) ), if( empty(item().Auditor3), '', concat( if(and(empty(item().Auditor1), empty(item().Auditor2)), '', ','), '{', '"ID": ', item().ID, ',', '"Location": "', item().Location, '",', '"Role": "Auditor3",', '"AuditorName": "', item().Auditor3, '",', '"AuditorEmail": "', outputs('Compose-Auditors')[item().Auditor3], '"', '}' ) ), if( empty(item().Auditor4), '', concat( if(and(empty(item().Auditor1), empty(item().Auditor2), empty(item().Auditor3)), '', ','), '{', '"ID": ', item().ID, ',', '"Location": "', item().Location, '",', '"Role": "Auditor4",', '"AuditorName": "', item().Auditor4, '",', '"AuditorEmail": "', outputs('Compose-Auditors')[item().Auditor4], '"', '}' ) ), if( empty(item().Auditor5), '', concat( if(and(empty(item().Auditor1), empty(item().Auditor2), empty(item().Auditor3), empty(item().Auditor4)), '', ','), '{', '"ID": ', item().ID, ',', '"Location": "', item().Location, '",', '"Role": "Auditor5",', '"AuditorName": "', item().Auditor5, '",', '"AuditorEmail": "', outputs('Compose-Auditors')[item().Auditor5], '"', '}' ) ), ']' ) )

Select_2

From

xpath( xml( addProperty( json('{}'), 'Root', addProperty( json('{}'), 'Item', body('Select') ) ) ), '/Root/Item/*' )

Map

json(item()).Item

Was this reply helpful? Yes No
CU27091846-0 17 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Chriddle,

I like this approach where Apply to each is avoided, the first select is giving me an error, I am sure it is related to the JSON function, when I remove it, the Concat function does work, can you advise if I am missing something?

Thank you

Was this reply helpful? Yes No
Chriddle 8,708 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Show me the error.
If it's related to the JSON function, the concatenated string probably is not valid JSON format. Can you show this string?

Was this reply helpful? Yes No