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

Getting just the Month number from a Date field in filter query

I have a Date field in my list and just want to extract the Month number.

 

Essentially i would like the query to be something like:

formatDateTime('Date','MM') eq '03'.
In order to get only the values from March from the list.
 
The above expression does not work.
How can i achieve this?
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

Hi!

No need for an 'apply to each' yet.

Please note the following

equals(formatDateTime(item()?['Date'], 'MM'), '03')

formatDateTime() output is a string, so you need to wrap 03 with single quotes

 

Can you run manually your flow, and inspect Filter array outputs? You will probably get an array of items matching your requirements? Hope there are just a few, if not we can add a 'SElect' action block just after the 'Filer array' for troubleshooting purposes

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

6 REPLIES 6
Highlighted
Dual Super User III
Dual Super User III

Hi!

Sharepoint list?

If so, are getting the info via 'Get item' or via trigger 'When a item is created'?

Or, did you get it by means of 'Get items', and you wanna get the trick inside an 'Apply to each' / 'Select' / 'Filter array'?

Thanks!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

My trigger is from a Powerapp. But I am using Get Items to get items from the Sharepoint list based on the filter query to then Apply to Each from the result. The filter query is based based on the Date from the Sharepoint list and the month number eq value is fed from the Powerapp. For an example, I am getting the month value in Powerapps using Month(StartDate)

Highlighted
Dual Super User III
Dual Super User III

Hi!

I think the problem is, when using oDATA expression in your Filter Query input, you need the following sintax:

 

ColumnName operator value

 

...but you cannot manipulate your column name by using

 

expression(Columname) operator value

 

The approach you have designed can work if you replace Filter Query and use a 'Filter array' action block just after 'Get items' instead, just need to assign 'Get items' output as 'Filter array' input. 

Does this new approach make sense to you?

Please let me know and we will start working on it



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

Okay. Its seems strange that you cant essentially substring a column before a compare.

 

I have added a Filter array with:

 

{
    "inputs": {
        "from""@body('Get_items')?['value']",
        "where""@equals(formatDateTime(item()?['Date'], 'MM'), 03)"
    }
}
 
Or adnaced. @equals(formatDateTime(item()?['Date'], 'MM'), 03)
 
So now i need to Apply to each on the body of this?
 
Thanks.
Highlighted
Dual Super User III
Dual Super User III

Hi!

No need for an 'apply to each' yet.

Please note the following

equals(formatDateTime(item()?['Date'], 'MM'), '03')

formatDateTime() output is a string, so you need to wrap 03 with single quotes

 

Can you run manually your flow, and inspect Filter array outputs? You will probably get an array of items matching your requirements? Hope there are just a few, if not we can add a 'SElect' action block just after the 'Filer array' for troubleshooting purposes

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Thanks, that has worked. Apply to Each on the body of Filter array is working correctly as well and producing the output i am expecting. I think this is solved.

 

Thank you for your help.

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (12,572)