cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tommyly Responsive Resident
Responsive Resident

What is the Proper Way to Filter and Group (Disambiguate)?

In the last several months, I've been learning quite a bit about PowerApp's syntax. From what I've read, I've been able to figure out one way of filtering out the latest date (per category), but it seems very inefficient. I'm sort of looking for a better way to do this.

 

I'll provide a hypothetical example and solution (which I'm sure is not the best way):

 

I have a SPO list of people's status. Consider an SPO list with these records:

  1. Title: "Away", PersonName: "Peter Griffin", DateOfStatus: 11/1/2018
  2. Title: "Present", PersonName: "Peter Griffin", DateOfStatus: 10/1/2018
  3. Title: "Away", PersonName: "Brian Griffin", DateOfStatus: 10/2/2018
  4. Title: "Vacation", PersonName: "Brian Griffin", DateOfStatus: 11/1/2018
  5. Title: "Present", PersonName: "Stewart Griffin", DateOfStatus: 11/1/2018

I want to create a collection that categorizes the list by PersonName and filtering only the latest dates:

  1. Title: "Away", PersonName: "Peter Griffin", DateOfStatus: 11/1/2018
  2. Title: "Vacation", PersonName: "Brian Griffin", DateOfStatus: 11/1/2018
  3. Title: "Present", PersonName: "Stewart Griffin", DateOfStatus: 11/1/2018

My intuition is to do something like this:

Filter(  
  AddColumns(ExampleList, "MainPersonName", PersonName),
  DateOfStatus= First(Filter(SortByColumns(ExampleList, "DateOfStatus", Descending), PersonName=MainPersonName)).DateOfStatus
)

In this example, I'm creating a temporary column called "MainPersonName" to act as the category and then my filter statement is essentially creating a subquery of a filtered-version of the same datasource. Is there a way to make this shorter or more efficient? I've read about disambiguation, but it's a bit confusing. Is there an opportunity to disambiguate here to make the syntax more efficient?

 

Thanks.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
tommyly Responsive Resident
Responsive Resident

Re: What is the Proper Way to Filter and Group (Disambiguate)?

@v-xida-msft

I've messed around with GroupBy a little bit. It sounds like that would work better for my needs--and I will use GroupBy instead.

 

 

View solution in original post

4 REPLIES 4
Super User
Super User

Re: What is the Proper Way to Filter and Group (Disambiguate)?

Possibly not the answer you are looking for but...

When data structures/processing become complex or you need to work with large amounts of data, I recommend using Azure SQL DB instead of SharePoint. You can then perform a lot more of the data filtering/processing in the Database (as well as more validation rules such as compound keys and much better delegation support) and just use PowerApps for what it does best - allowing the display of, and interaction with, the data.

The price of a single database, basic tier Azure SQL DB is $5 a month. Although it is a single database, for development you can always use different schemas to separate out the tables for different 'databases'. 

With the example you have given, you would just create a View that serves up the data in the desired format and add that as a datasource to your PowerApp. Quicker/easier to develop, more peformant and more flexible.

Community Support
Community Support

Re: What is the Proper Way to Filter and Group (Disambiguate)?

Hi @tommyly,

Do you want to filter your SP list items to filter out the latest date per category?

I think the GroupBy function could achieve your needs. I have made a test on my side, please take a try with the following workaround:10.JPG

Set the OnSelect property of the "Collect" button to following formula:

ClearCollect(
RecordsCollection,
DropColumns(
AddColumns(
GroupBy(SortByColumns('20181102_case15',"DateOfStatus",Descending),"PersonName","GroupData"),
"Title",
First(GroupData).Title,
"DateOfStatus",
First(GroupData).DateOfStatus
),
"GroupData"
)
)

On your side, you should type the following formula:

ClearCollect(
RecordsCollection,
DropColumns(
AddColumns(
GroupBy(SortByColumns('YourSPList',"DateOfStatus",Descending),"PersonName","GroupData"),
"Title",
First(GroupData).Title,
"DateOfStatus",
First(GroupData).DateOfStatus
),
"GroupData"
)
)

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

GroupBy function 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tommyly Responsive Resident
Responsive Resident

Re: What is the Proper Way to Filter and Group (Disambiguate)?

@PaulD1

Agreed. If I had an Azure SQL DB license, that would make my life 100x easier. I prefer to use SQL queries and views; unfortunately, the only resources I have at my disposal are SharePoint lists, Excel, and/or plain-text files.

tommyly Responsive Resident
Responsive Resident

Re: What is the Proper Way to Filter and Group (Disambiguate)?

@v-xida-msft

I've messed around with GroupBy a little bit. It sounds like that would work better for my needs--and I will use GroupBy instead.

 

 

View solution in original post

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (9,760)