Power Automate to Clear/Remove Filters from an Excel Table

[coolbeans23]

I have an Excel workbook (on SharePoint) with a table where sometimes users filters the data and save it.  Also, I have a flow that copies the table but sometimes the Excel table is filtered and does not copy all the data.  How can I remove/clear the filters before copying the table? I do not have Office Scripts.

My scheduled flow is followed by "Get file using path" action, then "List rows present in a table", then I tried to create a "Apply to Each" loop with the dynamic content 'value' from the previous step ("list rows present in a table") and a "Update a row" action inside the loop.  Key Column and Key Value fields are required for "Update a row" action and I tried putting one of the columns with the values from the data in the respective fields, although the flow ran it did not return the table unfiltered. Any advice?

[ManishSolanki]

Hi @coolbeans23 

As per my knowledge, "List rows present in a table" should return all rows irrespective of filter apply on the source table in excel sheet. Pls check if it returns all the records from the output in last flow run instance.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[coolbeans23]

You're correct, but because my Excel table has more records than it is allowed to (even after increasing the pagination setting to the max number  which is 5000) the data gets cut off.  Since I only need a certain number of records, in the "Get rows present in a table" action I used it to filter to the records I want to retrieve by putting the following in the Filter Query field: Invoice eq 'Past Due'

The Excel workbook is filtered to show one specific invoice that is past due, but there are other invoices that is past due that is being filtered out.

With my flow set up like mentioned above only the one specific invoice that is past due shows even though I'm using "Get rows present in a table" by narrowing records in the Filter Query field. How can I show all the invoices that are past due when the source data is filtered while I'm using the Filter Query to specify the records I want retrieved?

[dhair]

Here is what I would do using flow:

1. copy the entire excel with all your existing data into a temporary excel file
2. start a do-until loop
   1. get x amount of rows in each run
      • I would also make the amount of rows as an integer/variable, so that I can start with small chunks of data in each loop, say 10 rows, and later alter it to a larger number
   2. check the length of the data I got
      • if it is larger than 0, then continue to the next step.
      • if there is no data to process then that means your temporary file has been fully processed and can be deleted
   3. process each of the row as needed - so analyse, collect, save the result into a var or another excel file as needed
   4. delete each processed row
3. send an email notification that the process is completed

Have fun!