cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luvagoldenk9
Helper I
Helper I

Power Apps - Select Max Row for Each Group

Hello,

 

I have a table with CompanyName, Year, and Qtr (plus other fields but these are the ones I am interested in using). I am displaying a Gallery List, filtered companies for this year, then sorted CompanyName ascending and Qtr descending. The .Items statement is: 

 

SortByColumns(GroupBy(Filter('Sharepoint Co List', FY=varFY), "CompanyName", "Qtr", "GroupbyData"),"CompanyName",Ascending, "Qtr",Descending)

 

Which displays the following:

 

CompanyName   Year  Qtr

ABC Company     19     3

ABC Company     19     2

ABC Company     19     1

Bob's Co              19     2

Bob's Co              19     1

Dave's Co            19     1

Sue's Co              19     4

Sue's Co              19     1 

 

However, I only need to bring in the record with the latest Qtr for each unique CompanyName group. This would result in only the 'bolded' records being brought into the Gallery. Any thoughts/ideas on how to do this? TIA

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

Hi @luvagoldenk9 ,

According to your description, I've made a similar test.

Try this formula:

AddColumns(
GroupBy(tablename,"CompanyName","test"),
"MaxQtr",Max(test,Qtr),
"Year",LookUp(test,Qtr=Max(test,Qtr),Year)
)

Please replace the tablename with the name that you use.

 

 

Best regards,

Community Support Team _ Phoebe Liu

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yutliu-msft
Community Support
Community Support

Hi @luvagoldenk9 ,

According to your description, I've made a similar test.

Try this formula:

AddColumns(
GroupBy(tablename,"CompanyName","test"),
"MaxQtr",Max(test,Qtr),
"Year",LookUp(test,Qtr=Max(test,Qtr),Year)
)

Please replace the tablename with the name that you use.

 

 

Best regards,

Community Support Team _ Phoebe Liu

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

THANK YOU Phoebe for your solution! It worked perfectly and I really appreciate your assistance!!!!

Could someone please help me understand what 'test' represents in the solution? Is that a new column?

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,966)