Answered

SQL Table - Get Most Record for Each Unique ID

(0) Share

Report

Posted on by powerappsUser31

Data: SQL Table with 2 columns - ID & Date. There are approx 15 distinct IDs in the table, repeated a lot with different dates. In power apps, I'd like to see the most recent date for each ID in the table. I can't figure out the formula combination needed for this - any help ?

What i'd like it to look like:

ID | Date
1 | 5/7/2019
2 | 5/6/2019

Etc

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

mogulman 764 on at

Like (1)

Report
Copy link

Link copied!
I would create a view and do a filter in PA. I haven't tried but the view should look something like this. You may have to experiment with syntax.

CREATE VIEW [dbo].[MyView] AS SELECT Id ,RefDate ,ROW_NUMBER() OVER( PARTITION BY id ORDER BY RefDate DESC) AS Rn FROM MyTable
Rn will equal 1 for the max RefDate for each unique Id. Filter view where Rn = 1.

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @powerappsUser31 ,

Do you want to get the most recent date for each ID in your SQL Table within your SQL Table?

Based on the needs that you mentioned, I think the combination of GroupBy function, AddColumns function and DroColumns function could achieve your needs.

I have made a test on my side, please take a try with the following workaround:

On your side, you could add a Data Table control within your app, then set the Items property to following:

DropColumns(
AddColumns(
GroupBy('[dbo].[YourSQLTable]', "ID", "GroupData"),
"Date", /* <-- added new column */
First(SortByColumns(GroupData,"Date", Descending)).Date /* <-- Date represents the Date column in your SQL Table */
),
"GroupData"
)

then within the Data Table, enable the ID column and Date column to display.

More details about the GroupBy function, AddColumns function and DropColumns function, please check the following article:

GroupBy function, AddColumns function and DropColumns function

Best regards,

Was this reply helpful? Yes No
timl 37,152 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @powerappsUser31

If possible, I would recommend @mogulman's suggestion. I have used this exact technique in my own apps.

The benefit of using a view is that it'll be faster than a pure PowerApps solution. Also, it'll be more immune to delegation problems as the number of records in your table increases.

Was this reply helpful? Yes No
mogulman 764 on at

Like (0)

Report
Copy link

Link copied!

In general, I want to make the PowerApp as simple as possible. SQL Server is a very powerful and robust data storage solution. My view is SQL Server or Azure SQL DB should be the go to solution for any significant business application. Whenever possible use SQL Server to solve a problem. My only disappointment with PowerApps and SQL Server is PowerApps doesn't support SQL Server triggers. This needs to be fixed.

Was this reply helpful? Yes No