Power Automate

How to use a calculated column in Filter Query in Power Automate

(2) Share

Report

Posted on by Galazus

Hi Experts,

I'm struggling to build a filter to recognise the values in the calculated column in SharePoint.

That is the error message.

"Action 'Get_files_(properties_only)' failed: The field 'OverDueReminder' of type 'Calculated' cannot be used in the query filter expression"

It is a simple flow: it reads the ReviewStatus column and OverDueReminder Column's values, then updates ReviewStatus to a set status when the condition is meant, then fires off an email to remind the process owner to check the docs.

OverDueReminder Column is a calculated column the depends on the Next Review Due Date column's values then add 1 day (formular for that is =[Next Review Due Date]+1).

The Next Review Due Date triggers another automated email when the Next Review Due Date entry is due in the next 30 days. The logic behind the second reminder for the admins to monitor any docs whose status is not changed after 31 days.

My filter is below.

ReviewStatus eq 'Upcoming' and OverDueReminder le '@{addDays(utcNow(),31,'yyyy-MM-dd')}T23:59:59' and OverDueReminder ge '@{addDays(utcNow(),31,'yyyy-MM-dd')}T00:00:00'.

Those are my columns.

Any suggestions are welcome.

Thank you.

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Michael E. Gernaey 53,504 Super User 2025 Season 2 on at

Like (1)

Report

Hi,

You cannot use Calculated Columns in a Filter Query, it just isn't supported.

You would have to Filter based on what you CAN. Then you would add a Filter Array action and using your output from the previous (get items) value/body

as the input

Then in the filter click the little mapper and write the filter query you want against the returned rows.

Then you will have what you want.

Was this reply helpful? Yes No
Galazus 33 on at

Like (0)

Report

@FLMike,

Power automate is newish to me, so I'm trying to get more advanced in building flows.

Do you have an example or a similar post here I can follow? Especially that line you stated " add a Filter Array action and using your output from the previous (get items) value/body".

I can use get items with a filter for that column(Next Review Due Date), that returns any files due for review 30 days from today.

However, how can I use the Filter array action to pick up the files that will be overdue for review if their ReviewStatus is and stuck on "Upcoming" and OverDueReminder (calculated column) value is 31 days from today.

Note:

I have another flow that is working ok for this process, but that is only firing an email when the Review status is equal to Live and (Next Review Due Date) column's entry is equal to 30 days from today, then changes the Review status to Upcoming.

Later the document owner has to manually change the Review Status to "InProgress" when they start working on the file.

If the doc owner/process owner has not actioned the file by changing the review status to "InProgress", we want to send a reminder and change the ReviewStatus to OverDue 1 days after the (Next Review Due Date) has passed, hence the idea of using a calculated column which turns out the filters don't like.

The rest of the flow is straight forward, but I just can't get that logic to work via a filter conditions that uses a calculated column.

Thank you.

Was this reply helpful? Yes No
Suggested answer

abc 123 784 Moderator on at

Like (0)

Report

Store the calculated dates into a variable(s), and then use the variable(s) in the Filter Query.

There's an Action called Add Days that will do the date math.

Was this reply helpful? Yes No