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
Solved! Go to Solution.
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
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
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?
User | Count |
---|---|
264 | |
110 | |
92 | |
55 | |
41 |