cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JasonWeber
Frequent Visitor

Filter Query ne null doesnt work

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"
}
2 ACCEPTED SOLUTIONS

Accepted Solutions
v-bacao-msft
Community Support
Community Support

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:

67.PNG

Please take a try.

 

Best Regards,

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

View solution in original post

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.

85.PNG

Image reference:

84.PNG

 

Please take a try again.

 

Best Regards,

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

View solution in original post

7 REPLIES 7
v-bacao-msft
Community Support
Community Support

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:

67.PNG

Please take a try.

 

Best Regards,

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

View solution in original post

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".

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.

85.PNG

Image reference:

84.PNG

 

Please take a try again.

 

Best Regards,

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

View solution in original post

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.

Anonymous
Not applicable

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?

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.

jnguyen863
New Member

@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 ''

 

 

 

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.

Top Solution Authors
Users online (2,156)