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

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No

ciano789

28 on at

Like (0)

Report

@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

Was this reply helpful? Yes No

Suggested answer

David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Chriddle 8,443 Super User 2025 Season 2 on at

Like (1)

Report
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" } ]

Was this reply helpful? Yes No