Power Automate Expand Query To Join SharePoint Lists

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.

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.

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

Provide the following value for the Accept header to specify the response should be formatted as a JSON.

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']

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.

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

And we would need to add another Key-Value pair to the Map table of the Select action.

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.

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.