Answered

Remove records from collection if condition

(0) Share

Report

Posted on by PowerLiebhaberi

Hello,

I am struggling with an collection in my App. It has four columns - Date, Person, number and created. Acually it has more columns (holiday leave table, where people submit holiday but they can cancel as well), So basiclly it looks like below:

Date Person created

01.01.2023 Michael 01.02.2023

01.01.2023 Michael 01.03.2023

01.01.2023 Michael 01.04.2023

01.01.2023 Michael 01.05.2023

01.01.2023 Michael 01.06.2023

01.02.2002 Michael 01.06.2023

01.01.2023 Anna 01.02.2023

01.01.2023 Anna 01.03.2023

01.02.2002 Anna 01.06.2023

I'd like to count the rows of each person with the same date. If it's even (it means holiday is canceled), remove all records, if uneven, save only the the last created and remove the others. The result should look like this:

Date Person created

01.01.2023 Michael 01.06.2023

01.02.2002 Michael 01.06.2023

01.02.2002 Anna 01.06.2023

Does anyone have an idea? Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (2)

Verified answer

PowerLiebhaberi 14 on at

Like (0)

Report
This is my first post and I posted in the wrong forum. However I recieved an answer there which I'd like to share with the other amazing Powerappers :-)

ClearCollect( GroupedCollection, AddColumns( GroupBy( MyCollection, // your collection name. Date, Person, GroupedRecords ), RecordCount, CountRows(GroupedRecords), created, Last( SortByColumns( GroupedRecords, "created", SortOrder.Ascending ) ).created ) ); ClearCollect( FinalCollection, ForAll( GroupedCollection, If( Mod( RecordCount, 2 ) = 1, // checking is odd count. ThisRecord ) ) );

Was this reply helpful? Yes No
Verified answer

timl 36,393 Super User 2025 Season 2 on at

Like (0)

Report
Hi PowerLiebhaberi

Here's a formula to create the example collection that you posted.

ClearCollect( colHolidays, { Date: "01.01.2023", Person: "Michael", created: "01.02.2023" }, { Date: "01.01.2023", Person: "Michael", created: "01.03.2023" }, { Date: "01.01.2023", Person: "Michael", created: "01.04.2023" }, { Date: "01.01.2023", Person: "Michael", created: "01.05.2023" }, { Date: "01.01.2023", Person: "Michael", created: "01.06.2023" }, { Date: "01.02.2002", Person: "Michael", created: "01.06.2023" }, { Date: "01.01.2023", Person: "Anna", created: "01.02.2023" }, { Date: "01.01.2023", Person: "Anna", created: "01.03.2023" }, { Date: "01.02.2002", Person: "Anna", created: "01.06.2023" } );

This formula will build a collection that returns the records for a person with only a single date.

ClearCollect(colOddRecords, Filter(AddColumns( GroupBy( colHolidays, Date, Person, GroupedData ), RecordCount, CountRows(GroupedData) ), Mod(RecordCount,2) =1) );

Taking this grouped data, you can retrieve the created date (and other fields related to the last created record for the person) like so.

ClearCollect(colResult, AddColumns(colOddRecords, created, First(Sort(GroupedData,created,SortOrder.Descending)).created) );

This will give the result that you're looking for.

Was this reply helpful? Yes No