How to stack two list results together

[mnoah]

I have two SharePoint lists.

List1 has an ID of 39.  List two has a lookup column that points to List1 ID (in this case 39).  It is always a 1:1 relationship and list 2 will never have duplicate rows with the same lookup ID.

What I want to do is combine these two sharepoint list items in Power Automate and create one result by stacking the tables next to each other.  I've tried a Union() but it creates a an additional row.

Like this:

[abm]

Hi @mnoah 

Like you mentioned Union() expression doesn't work for the scenario you described. What you could do is read the first list then filter the list 2 using list 1 id. Get both results into a JSON format like below in a compose action step.

{

  "Id": 39,

  "Name": "Joe,John"

  "Date": "6/10/2022",

  "LookupID": 39,

  "Department": "HR",

  "Resource": "Yes"

}

Next map the compose output in an array (Append array)

This way you can build all the data in one row.

Thanks

} 

[mnoah]

Yes I could do that if I wanted to build the JSON object manually. But I have many columns of data and columns may be added in the future.  How to build it dynamically?

[abm]

Hi @mnoah 

I am not sure but have a look on SharePoint API or Graph API.

[mnoah]

@abmYes thank you I will send the lists to an Azure Function instead.