Get first row for each group

Like (0) Share

Report

Posted on 28 Dec 2023 12:45:44 by pleasehelpme2

I have a table for which I am trying to get the most recent record only for each Id according to the created column

ClearCollect(TestList, 
{Id: 1, created: 1, desc: "Lorem"}, 
{Id: 2, created: 3, desc: "Ipsum"}, 
{Id: 2, created: 2, desc: "Dolar"},
{Id: 3, created: 4, desc: "Sit"},
{Id: 1, created: 4, desc: "Amet"})

So I only want to keep the rows in green:

I thought I could group the table, sort each resulting table by the created column, keep the first record and then ungroup again.

ClearCollect(filteredList,
 Ungroup(
 ForAll(
 GroupBy(TestList, "Id", "Result"),
 {Id: ThisRecord.Id, Result: First(Sort(ThisRecord.Result, created, SortOrder.Descending))}),
 "Result")
)

However, the Ungroup function doesn't seem to be ungrouping the table in the results column and I am left with a grouped table:

Any help much appreciated!

All responses (7)

Answers (0)

timl 34,955 Super User 2025 Season 1 on 29 Dec 2023 at 11:07:08

Like (0)

Report

Re: Get first row for each group

@pleasehelpme2

With regards to the SharePoint issue and using a data table control, you can just set the items property of the data table control to the formula that I posted. If you use the AddColumns/First syntax, you can retrieve your Result.x Result.y, Result.z columns.
WiZey 3,023 Super User 2025 Season 1 on 29 Dec 2023 at 09:39:11

Like (0)

Report
Re: Get first row for each group

Hello @pleasehelpme2 ,

"Ungroup()" doesn't work because it's expecting a table, but "Result" here is a record.

I've written another formula, can you try and see how it goes:

ForAll( GroupBy( testList; "ID"; "Grouped" ); AddColumns( First( Sort( ThisRecord.Grouped; Created; SortOrder.Descending ) ); "ID"; ID ) )
pleasehelpme2 22 on 28 Dec 2023 at 19:02:52

Like (0)

Report
Re: Get first row for each group

The issue now is that if I set my table items to

Ungroup( ForAll( GroupBy(TestList, "Id", "Result"), {Id: ThisRecord.Id, Result: First(Sort(ThisRecord.Result, created, SortOrder.Ascending))}), "Result")

it only allows me to add two fields: Id and Result, when really I need to be able to display Result.x Result.y, Result.z etc.

So I suppose I go back to my first question, which is, why doesn't the ungroup work?
SebS 4,146 Super User 2025 Season 1 on 28 Dec 2023 at 14:43:30

Like (0)

Report

Re: Get first row for each group

@pleasehelpme2

replace name of test list with name of your SharePoint list and change names of the columns to match SharePoint List columns and you will be ok
pleasehelpme2 22 on 28 Dec 2023 at 14:33:52

Like (0)

Report

Re: Get first row for each group

Thanks for your answers. I actually want to show this in a table, and TestList is a sharepoint list. How can I do this?
timl 34,955 Super User 2025 Season 1 on 28 Dec 2023 at 13:41:40

Like (1)

Report
Re: Get first row for each group

@pleasehelpme2

Alternatively, you could also extract the required details by calling AddColumns and referencing the first Result row.

ClearCollect(filteredList, AddColumns(GroupBy(TestList,"Id","Result"), "created",First(Sort(Result,created)).created, "desc",First(Sort(Result,created)).desc ) )
SebS 4,146 Super User 2025 Season 1 on 28 Dec 2023 at 13:20:44

Like (1)

Report
Re: Get first row for each group

@pleasehelpme2

here :

ClearCollect(TestList, {Id: 1, created: 1, desc: "Lorem"}, {Id: 2, created: 3, desc: "Ipsum"}, {Id: 2, created: 2, desc: "Dolar"}, {Id: 3, created: 4, desc: "Sit"}, {Id: 1, created: 4, desc: "Amet"}); ClearCollect(filteredList, Ungroup( ForAll( GroupBy(TestList, "Id", "Result"), {Id: ThisRecord.Id, Result: First(Sort(ThisRecord.Result, created, SortOrder.Ascending))}), "Result") )

in gallery :

and labels You refer like this :

Hope that helps