Answered

How to Filter Out Blank Rows from Excel

(0) Share

Report

Posted on by Phineas

4,972 Super User 2025 Season 1

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?

All responses (5)

Answers (2)

Phineas 4,972 Super User 2025 Season 1 on at

Like (1)

Report

Re: How to Filter Out Blank Rows from Excel

I applied your solution.

It appears to be working.

Thank you.
Verified answer

v-wenjuan-msft Microsoft Employee on at

Like (0)

Report

Re: How to Filter Out Blank Rows from Excel

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.
Verified answer

v-wenjuan-msft Microsoft Employee on at

Like (0)

Report

Re: How to Filter Out Blank Rows from Excel

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.
Phineas 4,972 Super User 2025 Season 1 on at

Like (0)

Report

Re: How to Filter Out Blank Rows from Excel

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?
v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Re: How to Filter Out Blank Rows from Excel

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.