cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ClaireAllen
Post Prodigy
Post Prodigy

Filter Array Excel Date

Hello Community,

Can you help?

I have built a flow that gets my data from Excel and updates to a SharePoint list, but I'm having an issue with one of the columns in Excel, I only want to bring data in from 1st Jan 2020, but it still starts brining in data from 2014.

 

I tried putting a Filter Query in at List rows present in a table, but I received an error message

"Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.
clientRequestId: e45b88ca-1d74-4019-bd24-8c78f3b63f95
serviceRequestId: 5a26710b-a913-4bc5-9726-c224255be8d5"

 

So I found a post in the community about adding a Filter Array, but it does not work how I expect it to, so any help would be appreciated. I tried changing the date i.e. 2020-01-01 or 01/01/2020

Screenshot 2020-11-20 094250.png

Thank you in advance

Claire

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hello @Paulie78 ,

I would like to thank you for your help on Friday 20th, you kindly offered a Teams call to resolve my issue and by doing this, we managed to resolve the problems I was having and got my workflow running successfully,

I've put a screen shot in to show how the workflow was configured.

We entered some Compose actions to trouble shot out some of the issues, which really helped.

Step1.png

Step2.png

Step3.png

And kindly sent me a link to your blog.

Kind regards

Claire

 

 

 

View solution in original post

18 REPLIES 18
Paulie78
Super User
Super User

@Paulie78 ,

Thank for helping me again!

I've tried the link and I have my Filter array configured as below, I am trying to get information from 1st Jan 2020 onwards, I've change the greater than condition, but I can't seem to get this to work, I get an error message.

Screenshot 2020-11-20 114750.png

 

The flow fails at the filter array with this error message

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@greater(addDays('1899-12-30', int(item()['Start date agreed']), 'dd-MM-yyy'), '01-01-2020')' failed: 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

 

Any suggestions on how to fix where I went wrong?

Thank you & regards

Claire

 

Paulie78
Super User
Super User

Can you do this, go into the run history, click on list rows present in a table, download the content. Then put it into jsonlint.com. Go to the "Value" section and find your "Start Date Agreed" field, and then post a sample of what you find there please?

@Paulie78 ,

As requested, this is what I have in the Value section, Start date agreed

Screenshot 2020-11-20 124100.png

Hopefully this was the information that you required?

Thank you & regards

Claire

Paulie78
Super User
Super User

Yes it is. My guess is that you have a row in your table which has no value in "Start date agreed". Would that be correct?

Your expression is spot on, it is the empty rows causing the problem.

@Paulie78 ,

I do have empty rows, I did have an action for that within the Apply to each

Screenshot 2020-11-20 134505.png

I just can't get pass the Filter Array

Kind regards

Claire

Paulie78
Super User
Super User

Because the filter is failing before you get to your condition. Try something like this instead:

https://ibb.co/j9NRnxF

GetRidOfEmptyDates.png

Thank you,

I've done the suggestions, I tried the date both ways in the filter by start date, i.e 2020-01-01 & 01-01-2020 but I still get an error

Screenshot 2020-11-20 140051.png

Thank you & regards

Claire

Paulie78
Super User
Super User

Check the outputs in the run history of "Filter array 2" - Check that it did actually remove the blank items. You should only be providing valid input to "Filter array - StartDate". Also bear in mind that the input for "Filter array - StartDate" is no longer your Excel output, but the output from "Filter array 2 - RemoveBlanks"

abm
Super User
Super User

Hi @ClaireAllen 

 

Try to add the filter like below

 

image.png

 if(equals(item()?['DateOfBirth'],''),'01-01-1990',addDays('1899-12-30', int(item()?['DateOfBirth']), 'dd-MM-yyyy'))

 

Above expression I am checking whether the date is blank then default to an older date.

 

Note: Your are checking the date comparison so its better to use the format in yyyyMMdd format. This could be a better way to check.

 

if(equals(item()?['DateOfBirth'],''),'19900101',addDays('1899-12-30', int(item()?['DateOfBirth']), 'yyyyMMdd'))

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

@Paulie78 ,

I'm not 100% sure I understand the Filter array, this is how I have configured my workflow

Screenshot 2020-11-20 144448.png

Regards

Claire

@abm 

Sorry which expression as I meant to be using? I'm a little confused.

Also which Filter array am I updating in my example - RemoveBlanks or StartDate

I currently have my flow configured as below

Screenshot 2020-11-20 144448.png

Thank you & regards

Claire

Paulie78
Super User
Super User

what is your length expression in filter array 2? Should be something like:

length(item()?['Start date agreed'])

Yes it is,

Screenshot 2020-11-20 145948.png

abm
Super User
Super User

Hi @ClaireAllen

 

image.png

 if(equals(item()?['DateOfBirth'],''),'01-01-1990',addDays('1899-12-30', int(item()?['DateOfBirth']), 'dd-MM-yyyy'))

 

Above expression I am checking whether the date is blank then default to an older date.

 

Note: Your are checking the date comparison so its better to use the format in yyyyMMdd format. This could be a better way to check.

 

if(equals(item()?['DateOfBirth'],''),'19900101',addDays('1899-12-30', int(item()?['DateOfBirth']), 'yyyyMMdd'))

 

Thanks

 

 

 

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
abm
Super User
Super User

Hi @ClaireAllen 

 

You don't need two filters you can use one Filter Array so remove the 'Filter Array2 remove blank step'. Then use the expression which I shown in my example.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
krootz
Solution Supplier
Solution Supplier

@ClaireAllen how about this?

After the list rows, perform a select data operation to put all your fields into an array along with a new field (VALUE_CHECK) that equates to a 0 when the date is blank, otherwise, the excel date value.

 

The filter array would just be if VALUE_CHECK > 43831, which is equivalent to 01/01/2020.  

excel filter.png

Hello @Paulie78 ,

I would like to thank you for your help on Friday 20th, you kindly offered a Teams call to resolve my issue and by doing this, we managed to resolve the problems I was having and got my workflow running successfully,

I've put a screen shot in to show how the workflow was configured.

We entered some Compose actions to trouble shot out some of the issues, which really helped.

Step1.png

Step2.png

Step3.png

And kindly sent me a link to your blog.

Kind regards

Claire

 

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (3,052)