cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
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
Highlighted
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.

Highlighted
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.
Highlighted
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.

Highlighted
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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,879)