Unanswered

Complex filter based on sequential equality

(0) Share

Report

Posted on by martinav

3,347

Its not exactly a filter, but I didnt know exactly how to describe what I'm doing. I want to take a list of data. As shown:

What I wish to do... is to create some sort of loop that will take this data... use Valve_Type as the criteria... I want it to go down the list one by one.... Check if the current row is the same as the one before it. When it is not, I want it to create a row in a collection. Shown in this mockup below:

I will do some other complicated things, but I know how to do them. What I need to do is know how to do this efficiently. The source data is thousands of rows. I could do a forall(Data.Index, <formula>) but, I dont think you can break out of it once my criteria is not satisfied. It will have to go through thousands of rows for nothing, when its satisfied only in the first 5 rows.

GroupBy() will work either, because it accounts for the same value of the group column for the entire set. It wont stop squentially. Or, perhaps it will with some finessce. WHich, I do not know... so, I'm asking!!

Whatcha think?

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

timl 37,320 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

@martinav

My inclination would be to perform the calculation in SQL Server. That would be the most efficient way to do it if you have thousands of rows, and it would be immune to any delegation issues if you were to attempt this in Power Apps.

I'd experient with some of these Stack Overflow responses to find some TSQL that works with your source data.

https://stackoverflow.com/questions/23462434/pull-out-first-index-record-in-each-repeating-group-ordered-by-index

I'd then encapsulate the TSQL in a SQL view and connect to the view from Power Apps. Hopefully, this path will lead you to a working solution.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

@timl ,

I actually agree with you totally. However, I'm WAY less fluent in SQL, and the trial and error available in powerapps is far easier. I have done some complex things in SQL like you say, for VIEWS that have taken me weeks to completele with help from sql forums. I would do that if I needed to do this on a permanent basis. THis is a one time thing, fortunately. The data I showed you was an SQL table. It was a excel document with 90000 rows.

Once I do this, I will have created another SQL table from it, then I wont need PA any more. But, for the development of the statements, etc... its just far easier, and much less cryptic in PA over SQL. That is my opinion because I havent spent lots of time in SQL.

My approach was to work through the table in chunks. Each time I work through a chunk, I was going to have PA write back a '1' vale to a column "reviewed" then filter those out for the next grab into PA.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

This was not an answer. I'm still looking for how to do this in powwerapps.