Unanswered

Get first row for each group

(0) Share

Report

Posted on 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!

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

SebS 4,616 Moderator on at

Like (1)

Report
@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

Was this reply helpful? Yes No
timl 36,411 Super User 2025 Season 2 on at

Like (1)

Report
@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 ) )

Was this reply helpful? Yes No
pleasehelpme2 22 on at

Like (0)

Report

Thanks for your answers. I actually want to show this in a table, and TestList is a sharepoint list. How can I do this?

Was this reply helpful? Yes No
SebS 4,616 Moderator on at

Like (0)

Report

@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

Was this reply helpful? Yes No
pleasehelpme2 22 on at

Like (0)

Report
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?

Was this reply helpful? Yes No
WiZey 3,023 Moderator on at

Like (0)

Report
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 ) )

Was this reply helpful? Yes No
timl 36,411 Super User 2025 Season 2 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No