Joining/Merging Arrays

Hello!

I have two SharePoints that I am trying to Join/Merge in PowerAutomate: SP1 1 with the full list of vehicles & SP 2 with inspection times. Both share the same data in "Title." The inspections SP has a column called "Time"

Currently I have:

Get Items (gets items from SP1)

Get Items 2 (gets items from SP2)

Select (called Select-Inspection-Times)

From =

Outputs('Get_Items_2')?['body/value']

Map =

concat('"',item()?['Title'],'":',item())

Compose

Inputs =

json(concat('{',join(body('Select-Inspection-Times'),','),'}'))

Select

From =

Outputs('Get_Items')?['body/value']

Map =
- Vehicle =

item()?['Title']

Inspection Time =

outputs('Compose')?[string(item()?['Title'])]?['Time']

Currently, I am getting the correct data, but only the first iteration. So if I have in SP1:

and in SP 2:

A - 9:00
A - 10:00
B - 8:00

When I run the flow I get:

[
 {
 "Vehicle": "A",
 "Inspection Time": "9:00"
 },
 {
 "Vehicle": "B",
 "Inspection Time": "8:00"
 },
 {
 "Vehicle": "C",
 "Inspection Time": null
 }
]

I am hoping to be able to get something that looks like:

[
 {
 "Vehicle": "A",
 "Inspection Time": "9:00, 10:00"
 },
 {
 "Vehicle": "B",
 "Inspection Time": "8:00"
 },
 {
 "Vehicle": "C",
 "Inspection Time": null
 }
]

Any suggestions on what I need to do to get there? I feel like I'm close, but I can't get it to run with more than 1 iteration of each.

Thanks!

Hi @dsopshin

Your current code seems to taking first Inspection time entry, let's make the flow design little bit simpler

Consider this is the vehicle list

Consider this as inspection list

Now let's go to flow design

1. Get items from vehicle list

2. Select only Title column from the vehicle list using select action

3. get items from Inspection list

4. Select the title and Inspection Time column values from output of Get items of Inspection List

5. Use compose action to create an array structure like below

6. use select action to map each vehicle to its inspection timings (as required per output)

Expression -

if(empty(join(xpath(xml(outputs('Compose')),concat('//data[Title="',item(),'"]/InspectionTime/text()')),',')),null,join(xpath(xml(outputs('Compose')),concat('//data[Title="',item(),'"]/InspectionTime/text()')),','))

Now see the result-