Power Automate

List more than 300k rows and then filter

(0) Share

Report

Posted on by DineshK_

207

Hi All ,

I have a sharepoint excel file which has count of 333k rows.

I am trying to remove the rows with the filter below :

Email : contains 'noemail' , Country : Not GERMANY , Progress : Not Completed

but the "List rows in a table" action has a threshold of 100k only.

in that case how come i read all the 333k rows from the sharepoint and remove the satisfied condition rows.

I have tried below flow but not works.

Please help to achieve this task ...

Thanks

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Suggested answer

takolota1 4,980 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Please use the built in odata filter on the Excel list rows action with one of your filter conditions & a Filter array action for the remaining two, not a condition in a loop.

Otherwise your flow will take days to run, use up all your daily actions, & get throttled trying to slowly run for each of 333k records.

Was this reply helpful? Yes No
DineshK_ 207 on at

Like
a
(0)

Report
Copy link

Link copied!

@takolota1 I tried it already but as per the threshold limit in list rows , it will not search after 100k row right ?
correct me if i am wrong , if i add filter query of Progress ne 'Completed' , the list row action will look for all 333k to get this result right ? So after 100k row , it will not search further to satisfy the filter query condition.

most of the sources i referred saying to use Batch processing , but i don't have idea about it ..

Please help to guide the same ..

Was this reply helpful? Yes No
Suggested answer

takolota1 4,980 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I don't know of any limit on the filter to only the 1st 100k rows. Most connectors with an odata filter will still filter the entire datasource regardless of size & the 100k row limit only comes in after the filter is applied if you have more than 100k rows where Progress ne 'Complete'.

But if you really need to load in that many rows then I suggest using a set-up like the V1b on this template & adding your filters to the Filter array action inside the Do until loop.
https://community.powerplatform.com/galleries/gallery-posts/?postid=9f7a7bcf-c88f-ef11-ac20-7c1e525bd67d

Was this reply helpful? Yes No