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

[OE_APerron]

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:

length(body('Lister_les_lignes_présentes_dans_un_tableau')?['value'])

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

Thanks,

AP

[thjeffri]

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: 

length(items('Apply_to_each')?['Date'])

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

int(items('Apply_to_each')?['Amount'])

I hope this helps!

Tom