You’re offline. This is a read only version of the page.
I am using Excel (Business) 'List rows present in a table' with a Filter Query to find particular rows.
The function works fine if the table is unfiltered.
But if someone has opened the spreadsheet and filtered the table, the flow breaks with the following error message: 'Unable to match columns in the filtered view. [...] To use ordering or filtering, please try to make all the columns visible.'
Is there a way to get Flow to unfilter a table first, or to do some other workaround that searches the table even if it is filtered, or to find me the matching rows in another way?
Thanks
I got something kind of like the suggestion above to work using Excel scripts.
Share
Report
All responses (
Answers (function main(workbook: ExcelScript.Workbook) {
workbook.getTable("Table1").getRange("A:Z").setColumnHidden(false);
}
Sorry it's not very well formatted, but the "Table1" is the table that I'm working in, and the range "A:Z" is all of the columns in the table. I was able to use this in Power Automate via the Run Script action, but it has the odd effect of adjusting the width of all of the columns in my table that weren't hidden before. It looks like I should be able to create a script that hides specified columns again to run after if needed, just to put the table back in its previous view.
This problem is really annoying, did we find a workaround by staying with power automate, without using the "Automate" option of Excel?
Found a workaround using Excel 365's "Automate" tab in the ribbon, but this only works for those that have OneDrive for Business and a commercial Office 365 license (like "Business", "Enterprise", ...).
We can write a script that clears filters in a table automatically so scheduled flows can run without error. Click "New Script" to add a new script. Then enter the following code:
function main(workbook: ExcelScript.Workbook) {
let table1 = workbook.getTable("Table1"); // 'Table1' should be replaced with the target table name.
// Can add additional tables like below.
// table2 = workbook.getTable("Table2")
// Clear all filters
table1.clearFilters();
}
Be sure the rename the script with something memorable.
Now back to Power Automate in Edit Flow, a "Run Script" Action can be added just before whatever Excel row action needs to be accomplished. This can be found under "Excel Online (Business)" while adding a new Action.
Now that the target Excel action is working with unfiltered Table(s), this particular error goes away. Or at least it worked for me.
Resources:
https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel
https://learn.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration
Do we know why this issue is appearing, I have the same issue.
now I applied the work around to use the filter action after get the full set of the table.
I have check the excel output with out the filter, it doesnt seems missing any record. very weird.
Hi @BenHarland ,
Have you tried copying an unfiltered table and creating a new table specifically for configuring List rows present in a table?
In the current situation, it seems to be a method that can be considered.
Best Regards,
#1
stampcoin
61
#2
Michael E. Gernaey
47
Super User 2025 Season 1
#3
rzaneti
31
Super User 2025 Season 1
