Filter by calculated fields in SharePoint using Power Automate

When you read items from a SharePoint list and you want to filter by calculated fields in that list filter queries will fail. So how can you filter your items?

A SharePoint list with a calculated field

In SharePoint I’ve created a list with a calculated column. These calculations can become quite complicated and sometimes you might just want to create a flow that does the calculations for you.

There are however plenty of reason to create calculated columns. In my example I’m using the created column to calculate tomorrow.

Then as i create items in my list I see that Tomorrow has been set to tomorrow’s date.

Get Items from SharePoint

When you use the experimental filter query option you will see that the calculated columns aren’t listed in the drop down and also when you use the GA version of the same things will not be much better

When you configure the query with add a custom item in the above action, then you will see the following error when you run the flow:

The field ‘Tomorrow’ of type ‘Calculated’ cannot be used in the query filter expression.

Filter by calculated fields

So that means that we can’t filter the items during the get items action and we will need an additional filter action added to our flow.

Condition Left: formatDateTime(item()?[‘Tomorrow’], ‘d’)

Condition right: formatDateTime(AddDays(utcNow(), 1), ‘d’)

When we run the flow with this additional fitler action, the flow is successful and we managed to filter by calculated fields in SharePoint.

Share
Pieter Veenstra

Business Applications and Office Apps & Services Microsoft MVP working as a Microsoft Productivity Principal Consultant at HybrIT Services. You can contact me using contact@veenstra.me.uk.

View Comments

Recent Posts

Create a Microsoft Teams team template

Recently I was asked about creating a team template in Microsoft Teams and apply this…

1 week ago

Who is in the office tomorrow, using Power Automate?

Do you want to know who is in the office tomorrow or the day after?…

2 weeks ago

8 top reasons to use Dataverse or SharePoint lists in the Power Platform

Why would you use Dataverse, if SharePoint lists gives you the option to avoid premium…

4 weeks ago

Expected String but got Null, when calling a flow from a Model Driven app

A few weeks ago I looked at calling flows from model driven apps. And for…

1 month ago

Get all your broken connections using Power Automate

To get all your broken connections in the Power Platform can be a challenge. In…

1 month ago

Customer Voice a.k.a. Forms Pro the database behind the magic

If you call Customer Voice still Forms Pro or not, have you ever looked into…

1 month ago
%%footer%%