cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OE_APerron
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:

 

OE_APerron_0-1595180250576.png

 

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:

OE_APerron_1-1595180423250.png

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
1 REPLY 1
thjeffri
Microsoft
Microsoft

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:

 

chrome_79QcT7TXpA.png

 

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

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,567)