Showing results for 
Search instead for 
Did you mean: 
New Member

Filter out Blank Rows from the output of a <List rows present in a table> action in order to Add items as Rows to another Excel Table

Hello all,


I'm trying to optimize a flow that currently picks up an *.xls file in attachment of an email, converts it in *.xlxs , then creates an Excel Table in one specific Tab with the intent to List Rows, and Add a Row to another Excel Table stored in SharePoint.


Here's my challenge:


1) The Tab used to create the "temporary Table" contains material orders passed for a 15 days period, therefore I can't guess the number of lines that will contain that Table. Therefore when asked to specify the Table Range, I have a theoretic range that I know will never be above:




2) When I use the action List Rows in a Table, I'd like to pick up only the lines that are not "Empty" otherwise, it creates 9999 lines and runs for 2-4 hours.


I've tried different options seen in this forum (filter Array, set Variable, ODATA filter), but could never make it work, my last attempt being:


If you ever had a similar issue and a solution/option I could try, please respond to this message.

Hi OE_APerron,


I think you have the right idea here, which is to use a condition to check each row to see if it actually contains data.  There is an issue to the execution, though.


The condition you are currently using is actually going to use length() to check the number of items returned by the "List rows present in a table" action.  Length() can also be used to check string length, but the content needs to be converted to a string first.  Even if you do that, though, empty Excel rows still have properties like ItemInternalId which makes the string length > 0.


I would recommend doing a separate evaluation for each column on each row.   Something like this:




My test table only had two columns so this was pretty simple for me.  You would likely need one expression for each column in your table.  Exactly what these expressions are will depend on your data types.  For example, I have a column called Date which was stored as a string, so I evaluated that as: 

And I had an integer column called Amount which was also stored as a string, so for that my expression was:
I hope this helps!

Helpful resources

Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,420)