cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hjaf
Level 8

A better way to make "Excel get rows" ignore table filters set in the file?

The get rows from table action considers all filters set in the table (with excel editor), this is a headache because people are applying different filters all the time on the tables flow works with. One of my flows looks for rows that match IDs from a SharePoint-list and updates all the rows that match. But if a user has applied a filter that hides the matching rows, the flow fails or does not find the data. I thought filters was only visual aid in excel to organize the content of tables and not manipulate the data itself. Perhaps I misunderstood, but if that is the case, I find it to be somewhat of a paradox that PowerQuery and even named references in excel ignores these filters. Furthermore, I can manipulate hidden rows with other excel actions in flow, It can even even read individual rows, so why not be able to read all of them? is this a bug? or "its not a bug, its a feature!" 

 

I thought about making a query that queries the table within the file and then have flow read that table instead, the problem is that people often use the web-editor which prevents this query to update regularly. 

 

The best I have come up so far is to add a secondary table on hidden sheet with references to the columns in the primary table, so flow can pinpoint the rows to read and update in the primary table. But this still feels wrong, as this basically adds a useless table of about 10000 lines that increases the file size for one thing.  

Anyone got a better or correct way to get around this limitation/bug/feature? Perhaps there is a completely different approach available?

image.pngthe screenshot..

1 REPLY 1
Super User
Super User

Re: A better way to make "Excel get rows" ignore table filters set in the file?

@hjaf 

Hi there!  This is interesting, I wouldn't have guessed that Excel would behave that way.

What do you think about this idea:

 

Rather than duplicating the sheet in another sheet, Create a new Excel Workbook, use "Get Data" to grab the table from the first workbook, and then build your Flow from the second workbook.

 

Since the data is really only residing in one place, the file sizes should be manageable and your second workbook should be updated as the original is automatically.

 

Either way, please keep us posted.

-Ed-

 

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Helpful resources

Announcements
firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (6,447)