Power Automate Expand Query To Join SharePoint Lists

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/power-automate-expand-query-to-join-sharepoint-lists/. Author: Matthew Devaney.

Power Automate Expand Query To Join SharePoint Lists Posted by - Matthew Devaney on - October 6, 2024 5 Comments

When you get items from a SharePoint list in Power Automate an expand query can add columns from a related SharePoint list. To do this one of the SharePoint list musts have a lookup to the other list. Then we can Send An HTTP Request to SharePoint and write an expand query to join together columns from both tables and return a single array as the result. Table of Contents

Introduction: Join Related SharePoint Lists With An Expand Query Setup The Related SharePoint Lists

Create A SharePoint Get Items HTTP Request With An Expand Query

Select The Columns From The SharePoint HTTP Request

Run The Flow To View Expand Query Results

Add Multiple Related List Columns To The Expand Query

Completed Flow Code: Expand Query To Get Related List Columns

Introduction: Join Related SharePoint Lists With An Expand Query

A fleet manager at a construction company wants to create a report on who reserved a vehicle and which vehicle they reserved. The information is stored in two separate SharePoint lists: Vehicle Reservations and Company Vehicles .

Using an expand query the fleet manager is able to join the two lists together and combine their data in a single array. A Setup The Related SharePoint Lists

Create a new SharePoint list named Company Vehicles with the following columns: ID – unique identifier YearMakeModel – single line of text LicensePlate – single line of text Office – single line of text AssetCode – single line of text

Populate the Company Vehicles SharePoint list with this data. ID YearMakeModel LicensePlate Office AssetCode 1 2020 Dodge Ram Y2K 9D9 Albany 10-023 2 2016 Ford F150 Q9T 9T5 Albany 10-034 3 2019 GMC Sierra A7I 0Z5 Fargo 10-023 4 2020 Honda Ridgeline J9B 1P8 Schaumberg 10-021 5 2021 Nissan Pathfinder Q7K 7L7 Schaumberg 10-301

Then create another new SharePoint list named Vehicle Reservations with the following columns: ID – unique identifier

Vehicle – lookup to the Company Vehicles table Employee – single line of text StartDate – date only EndDate – date only

Fill-in the Vehicle Reservations SharePoint list with these values. ID Vehicle Employee StartDate EndDate 1 3 Mark Clark 1/25/2021 1/29/2021 2 2 Anna Sinclair 1/25/2021 1/27/2021 3 4 Laura Andrews 1/27/2021 1/29/2021 4 1 Sarah Green 1/25/2021 1/25/2021 5 1 John Freeman 1/26/2021 1/27/2021 6 3 Laura Andrews 2/1/2021 2/5/2021 7 3 Mark Clark 2/10/2021 2/10/2021 8 5 Anna Sinclair 2/13/2021 2/14/2021

Create A SharePoint Get Items HTTP Request With An Expand Query

We want to get the Vehicle Reservations SharePoint list item along with the YearMakeModel column from the Company Vehicles SharePoint list in a single flow action. To do this we will create an expand query. And expand query allows us to retrieve any column from a related entity by targeting a lookup field.

Create a new flow with an instant trigger and add a Send An HTTP Request to SharePoint action. Use the GET Method to retrieve items from a SharePoint list.

Use this code in the Uri field to get the Id and Employee fields from the Vehicle Reservations SharePoint list and the YearMakeModel column from the Company Invoices list. More information on how to use the $select and $expand query parameters can be found in the documentation for the SharePoint REST API .

_api/web/lists/GetByTitle( 'Vehicle Reservations' )/items?$select=Id,Employee,Vehicle/YearMakeModel&$expand=Vehicle/YearMakeModel Code language: PHP ( php )

Provide the following value for the Accept header to specify the response should be formatted as a JSON. Header Value Accept application/json;odata=verbose

Select The Columns From The SharePoint HTTP Request

The Send An HTTP Request to SharePoint action returns more data than we want in our array. Add a Data Operations – Select action to get only the columns we want.

Write this code in the From field of the Select action to use the SharePoint HTTP request results.

body( 'Send_an_HTTP_request_to_SharePoint:_Get_Items' )?[ 'd' ]?[ 'results' ] Code language: JavaScript ( javascript )

Then add these Key-Value pairs to the Map table of the Select action. Expressions found in the Value column must be entered using the Power Automate expressions editor. Key Value ID item()?[‘ID’] Employee item()?[‘Employee’] YearMakeModel item()?[‘Vehicle’]?[‘YearMakeModel’]

Get The Dynamic Values For List Items Using Parse JSON

A Select action will only show Outputs in the dynamic values list by default. To get the ID , Employee and YearMakeModel columns to appear we will need to parse the select action. Add a Data Operations – Parse JSON action and include the Select action Outputs as Content. Generate the JSON schema from a sample.

Now the ID , Employee and YearMakeModel appear in the Dynamic Values list.

Run The Flow To View Expand Query Results

That’s all it takes to write an expand query. Test the flow to ensure it gives the expected results.

The output of the Parse JSON action shows an array with values from both SharePoint lists.

Add Multiple Related List Columns To The Expand Query

An expand query can be used to get multiple columns from a related SharePoint list. Suppose we also want to get the LicensePlate column from the Company Vehicles SharePoint list and and join it with results from the Vehicle Reservations list.

The Uri field of the Send An HTTP Request To SharePoint action would be updated to look like this.

_api/web/lists/GetByTitle( 'Vehicle Reservations' )/items?$select=Id,Vehicle/YearMakeModel,Vehicle/LicensePlate&$expand=Vehicle/YearMakeModel,Vehicle/LicensePlate Code language: PHP ( php )

And we would need to add another Key-Value pair to the Map table of the Select action. Key Value LicensePlate item()?[‘Vehicle’]?[‘LicensePlate’] Re-run the flow to check the result…

…and confirm there are now 4 columns in the array: ID, Employee, YearMakeModel, LicensePlate.

Completed Flow Code: Expand Query To Get Related List Columns

An image of the finished flow can be found below. Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up Questions?

If you have any questions or feedback about Power Automate Expand Query To Join SharePoint Lists please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion. Matthew Devaney Power Automate

Save Docusign Document To SharePoint With Power Automate