Filter Query ne null doesnt work

(1) Share

Report

Posted on by JasonWeber

I'm building a flow that pulls a spreadsheet into a list.

However I cannot guarentee that the source spreadsheet (table) WON'T contain empty rows.

I use the Filter Query as:

filter query.PNG

However it instantly errors with:

{

"status": 400,

"message": "Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.\r\nclientRequestId: 0e0f156b-74cf-4f10-97fc-0d16324a41d6",

"source": "excelonline-ase.azconn-ase.p.azurewebsites.net"

}

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (2)

Verified answer

v-bacao-msft on at

Like (2)

Report

Hi @JasonWeber ,

I had a test on my side, and Filter Query is not valid for filtering null values.

You could use Filter array to filter the rows whose corresponding fields are not empty.

Image reference:

Please take a try.

Best Regards,

Was this reply helpful? Yes No
JasonWeber 11 on at

Like (1)

Report

Firstly, sorry, Ive been on leave.

So what you have described there *seems* to DELETE any rows that DO have content?

If I change it to 'is equal to' (empty) and thus (in theory) I'm deleting empty row's - it errors.

If I try a different action (eg: Create Item (SharePoint List) it doesnt filter and gives me a heap of List Items with "No Title".

Was this reply helpful? Yes No
Verified answer

v-bacao-msft on at

Like (2)

Report

Hi @JasonWeber ,

Your requirement is that there may be blank rows in Excel table, so you want to exclude these empty rows and then insert non-empty rows into the list.

But the method you use is actually invalid, because using "ne null" in Filter Query is invalid, so I recommend using Filter array to filter out non-empty rows first.

My method currently seems to have problems, it need to add more restrictions to eliminate empty rows.

Please try to use Condition directly. For example, I have five fields, we need to make sure that all five fields are not empty before executing Create item action.

Image reference:

Please take a try again.

Best Regards,

Was this reply helpful? Yes No
JasonWeber 11 on at

Like (1)

Report

Ah-HAH!
Thanks worked perfectly.
Because doing Excel -> SPO list requires the creation of a table in the spreadsheet and I cannot guarentee the number of rows, I need to do the table creation much longer than the number of rows to make sure nothing gets chopped off at the end.
The downside of this is that I bound to end up with numerous empty rows at the tail end of the table.

FWIW - the *entire* process is something like:
Take automated csv output from another system.
Convert to Excel
Wrap the data in a table.
Empty the existing SPO list (because I was never able to work out how to do an 'look for changes and update')
Then finally repopulate the list based on the new/nightly spreadsheet.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (4)

Report

Hello,

Just to confirm, is there any syntax within the Filter Query portion of List Rows where I can filter out null values without having to manipulate the data later?

Was this reply helpful? Yes No
980Panama 140 on at

Like (2)

Report

This issue was a long time ago but I have just had the same issue. Does seem very odd that using a ne null should cause a problem in the filter query, appears more like a bug than a feature as "ne" is certainly allowed in the filter. I seem to have got over this problem by using gt (greater than) null and this works.

Was this reply helpful? Yes No
jnguyen863 8 on at

Like (4)

Report
@980Panama Sorry I am late to the game. I took a real simple approach. Instead of filtering by "null", I literally filtered it by nothing! Using condition is handy, but increases your flow execution time.

cost_centre ne ''

Was this reply helpful? Yes No
AndreasO 87 on at

Like (2)

Report

This actually works. This is amazing. Thank you so much.

Was this reply helpful? Yes No
404 Not Found 11 on at

Like (1)

Report

Thanks for the idea of using gt ''

It is a sad work around, but I have more than 5,000 rows with null values, so they really need to be filtered out at the beginning.

Was this reply helpful? Yes No
selo0910 on at

Like (1)

Report

So simple yet so effective.
I salute you for this solution!

Was this reply helpful? Yes No