cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martinav
Super User
Super User

Complex filter based on sequential equality

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:

martinav_1-1651789717874.png

 

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:

 

martinav_2-1651789921983.png

 

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? 

 

3 REPLIES 3
timl
Super User
Super User

@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-ord...

 

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
Super User
Super User

@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
Super User
Super User

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

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,224)