Answered

How to Filter Out Blank Rows from Excel

(0) Share

Report

Posted on by Phineas

5,331 Moderator

An Excel attachment arrives in an email box.

A peer has a flow that moves the attachment content to an Excel document in OneDrive.

That flow is formatted so that the Excel 'table' parameters are A:O, rather than A1:O500 (for example).

I have a flow that pulls the content from the OneDrive Excel document and updates SPList1.

As currently formatted (I have no control over fixing the table parameters), this causes SPList1 to have dozens of blank rows.

Is there a way, I my end, that I can filter/query OUT - in my 'List rows present in a table' or elsewhere, the blank rows coming in, so as to not populate SPList1 with dozens of blank rows?

The return is 947 when there are actually only 606 items/rows with content.

How can I update the formula to NOT count the blank rows?

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (2)

Sort by

v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Phineas ，

What you would like to do is to retrieve data from Excel and remove the blank rows, then add the remaining ones into a SharePoint list, right?
Refer to my demo for solution:

In my test data, if column E is blank, the row will be blank. So I filter out rows owning blank E, and operate on the remaining rows next.

length(item()?['E'])

Here's test data and result:

Community Support Team _ Wenjuan Zou
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Just so I understand, the 'length' expression is what is filtering out the blanks, yes?

Meaning, if I know of a column that is blank through all rows that are blank I should use that column in the filter array 'length' expression, yes?

Was this reply helpful? Yes No
Verified answer

v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Correct.

Does it work for you? You may mark the thread as resolved if my solution works for you.

Community Support Team _ Wenjuan Zou
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
Verified answer

v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Phineas ,

May I know is there any progress or do you need any further help?

Community Support Team _ Wenjuan Zou
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (1)

Report
Copy link

Link copied!

I applied your solution.

It appears to be working.

Thank you.

Was this reply helpful? Yes No