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 ,

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 blogPower 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 blogPower 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 blogPower 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

 

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

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 (1,605)