Power Automate

Group by various columns and return max date

(0) Share

Report

Posted on by ciano789

Hi All,

I am stuck.

I have a sharepoint list with the following columns:

Module (Dropdown list),

Workstream Dropdown List)

Milestone (Dropdown List)

Milestone Start Date (Date Field)

Milestone End Date (Date Field)

Deliverable (Dropdown List)

Deliverable Start Date (Date Field)

Deliverable End Date (Date Field)

Start Date (Date Field)

End Date (Date Field)

The goal is to create a Power Automate flow that groups the data and finds the max start date for the group and update the Start Date column and do the same with the end date.

The logic will be something like

If Deliverable is not blank then group by Module, Workstream, Milestone, deliverable and return max start date,

Else Group by Module, Workstream, Milestone and return max start date (and again do the same for end date)

Categories:

General topics

All responses (4)

Answers (0)

Sort by

Suggested answer

David_MA 15,508 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

What do you mean by grouping the data? When you use the Get items action in Power Automate, it returns an array of items. You can’t group them directly within the action. If you’re looking to return a specific set of items, you would use filtering instead. You can also sort the data by one of your date fields using desc (newer to older) or asc (older to newer). If you want to find the maximum or minimum value, you can limit the action to return just one row.

ciano789

28 on at

Like (0)

Report

Copy link

Link copied!

@David MA

I want to group by a combination of values see my example below.

I want the flow to update the overall start date and end date columns.

To do this I want to group by Module, Workstream and find the earliest date within the Milestone start date or Deliverable Start date and return this to the overall start date column.

See in the below example the first 2 rows have the same Module, Workstream & Milestone so for those 2 rows I want to look at Milestone start date and Deliverable Start date, get the earliest value and return it to the overall start date column.

Module	Workstream	Milestone	Milestone Start Date	Milestone End Date	Deliverable	Deliverable Start Date	Deliverable End Date	Overall Start Date	Overall End Date
1	WS1	M1	01/01/2024	02/01/2025	D1	03/01/2024	04/01/2025	01/01/2024	31/12/2025
1	WS1	M1	01/01/2025	31/12/2025	D3	03/01/2025	28/12/2025	01/01/2024	31/12/2025
2	WS2	M2	02/05/2024	23/08/2024	D4	15/05/2024	30/08/2024	02/05/2024	30/08/2024
2	WS3	M2	21/06/2024	31/12/2025	D5	29/06/2024	30/12/2025	21/06/2024	31/12/2025

Suggested answer

David_MA 15,508 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

I think what you're looking for is to create an HTML table. Again, there is no way to "group" in Power Automate. You may be able to achieve this by a combination of the filters and sorting. You can refer to this on how to use the Select and Create HTML table actions: Use data operations in Power Automate - Power Automate | Microsoft Learn. Your data for the Select action will come from the Get items action instead of an HTTP request.
Suggested answer

Chriddle 8,725 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
Grouping is possible using XPath ;)

Compose-Data

Your data (only relevant properties)

"inputs": [ { "Module": "1", "Workstream": "WS1", "Milestone": "M1", "Milestone Start Date": "01/01/2024", "Deliverable Start Date": "03/01/2024" }, { "Module": "1", "Workstream": "WS1", "Milestone": "M1", "Milestone Start Date": "01/01/2025", "Deliverable Start Date": "03/01/2025" }, { "Module": "2", "Workstream": "WS2", "Milestone": "M2", "Milestone Start Date": "02/05/2024", "Deliverable Start Date": "15/05/2024" }, { "Module": "2", "Workstream": "WS3", "Milestone": "M2", "Milestone Start Date": "21/06/2024", "Deliverable Start Date": "29/06/2024" } ]

Select-Data2

The data needs some changes: sortable dates and no special characters in property names (to avoid encodings in XML).

"inputs": { "from": "@outputs('Compose-Data')", "select": { "Module": "@item()['Module']", "Workstream": "@item()['Workstream']", "Milestone": "@item()['Milestone']", "MilestoneStartDate": "@parseDateTime(item()['Milestone Start Date'], 'uk-en', 'dd/MM/yyyy')", "DeliverableStartDate": "@parseDateTime(item()['Deliverable Start Date'], 'uk-en', 'dd/MM/yyyy')" } }

Select-Keys

Create the keys

"inputs": { "from": "@body('Select-Data2')", "select": { "Module": "@item()['Module']", "Workstream": "@item()['Workstream']", "Milestone": "@item()['Milestone']" } }

Select-Result

Make the keys unique

Use xpath to query the data for the keys, get the dates for this keys, sort them and return the first

"inputs": { "from": "@union(body('Select-Keys'), json('[]'))", "select": { "Module": "@item()['Module']", "Workstream": "@item()['Workstream']", "Milestone": "@item()['Milestone']", "Milestone Start Date": "@first(sort(xpath(xml(json(concat('{\"Root\":{\"Item\":', body('Select-Data2'), '}}'))),concat('//Item[Module=\"',item()['Module'], '\" and Workstream=\"', item()['Workstream'], '\" and Milestone=\"', item()['Milestone'], '\"]/MilestoneStartDate/text()'))))", "Deliverable Start Date": "@first(sort(xpath(xml(json(concat('{\"Root\":{\"Item\":', body('Select-Data2'), '}}'))),concat('//Item[Module=\"',item()['Module'], '\" and Workstream=\"', item()['Workstream'], '\" and Milestone=\"', item()['Milestone'], '\"]/DeliverableStartDate/text()'))))" } }

The xpath expression for "Milestone Start date" looks like this:

first( sort( xpath( xml(json(concat('{"Root":{"Item":', body('Select-Data2'), '}}'))), concat('//Item[Module="',item()['Module'], '" and Workstream="', item()['Workstream'], '" and Milestone="', item()['Milestone'], '"]/MilestoneStartDate/text()') ) ) )

The result of the flow run is the following array:

[ { "Module": "1", "Workstream": "WS1", "Milestone": "M1", "Milestone Start Date": "2024-01-01T00:00:00.0000000", "Deliverable Start Date": "2024-01-03T00:00:00.0000000" }, { "Module": "2", "Workstream": "WS2", "Milestone": "M2", "Milestone Start Date": "2024-05-02T00:00:00.0000000", "Deliverable Start Date": "2024-05-15T00:00:00.0000000" }, { "Module": "2", "Workstream": "WS3", "Milestone": "M2", "Milestone Start Date": "2024-06-21T00:00:00.0000000", "Deliverable Start Date": "2024-06-29T00:00:00.0000000" } ]