Answered

Reduce Table to Latest Record for Each User

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I am working on a PowerApp that pulls data from an Azure SQL instance. This table has multiple records per person and I would like to create a collection of just the latest record per user. Example:

CurrentTable

Name	ApplicationState	DateRecordCreated
Shane	Terminated	2022-05-22
Marry	Pending	2022-07-15
Bob	Active	2022-06-11
Sonya	Active	2022-05-22
Shane	Active	2022-07-05
Bob	Pending	2022-05-22
Marry	Terminated	2022-05-22

I would like to reduce CurrentTable down to the latest records per user, NewTable:

NewTable

Name	ApplicationState	DateRecordCreated
Shane	Active	2022-07-05
Bob	Active	2022-06-11
Marry	Pending	2022-07-15
Sonya	Active	2022-05-22

I have tried nested filtering and using GroupBy within a filter. I feel like I've gotten close but can't quite get what I'm looking for. Thank you in advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@Anonymous

Please consider the following Formula:

ForAll( GroupBy( Sort(CurrentTable, DateRecordCreated, Descending), "Name", "_items" ), {Name: Name, ApplicationState: First(_items).ApplicationState, DateRecordCreated: First(_items).DateRecordCreated } )

This would return a table like you are looking for.

I hope this is helpful for you.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

That looks interesting, I will try to apply that now. The real table I'm working with has many more columns. Would you mind explaining to me what the code is doing?

Thanks

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
@Anonymous

What the formula is doing is to first sort the table by the created date. Then it groups those results by the "Name" column. All associated records will then be in the "_items" column. Since the list was sorted in descending date order, the first record of each individual group will be the most recent.

So, the ForAll then is returning the table of records that would be the group name, and then the first record values.

If you have a lot of columns to contend with, then you can change to the following Formula so that you don't need to specify each column by hand:

ForAll( GroupBy( Sort(CurrentTable, DateRecordCreated, Descending), "Name", "_items" ), Patch({Name: Name}, First(_items)) )

Was this reply helpful? Yes No