Workaround for Using Calculated Columns in Power Automate Filter Queries

Ever wonder how to use a calculated column in Power Automate? I faced a similar challenge while automating subscription reminders when Power Automate wouldn’t let me use a calculated column in a Filter Query. But don’t worry—I found a workaround! Let’s dive in and see how I cracked this code step-by-step.

Understanding the Challenge

When I was developing a gym management app with Power Apps, I ran into a frustrating issue: I had a calculated column in my SharePoint List that showed when members’ subscriptions were due, but Power Automate wouldn’t allow me to use this column to filter data directly. It was like trying to fit a square peg into a round hole…nope, not happening! 😅😅😅

Overview of the Gym Management App

So, just a quick overview. The app’s main purpose was to manage memberships, tracking everything from sign-ups to renewals. I used Power Apps for the app interface, SharePoint as the data source, and Power Automate to manage those all-important subscription reminders. My SharePoint List was set up for this app to store essential member details, including the subscription due date. This due date was calculated automatically, based on when the member joined and their subscription length. But little did I know, this calculated column would soon turn into a bit of a headache.

The Need for Subscription Reminders

Let’s face it: gym members are busy. Sometimes they forget when their subscription is due, and a little nudge could go a long way in ensuring everyone stays active and on track. Automated reminders make it super easy to keep members in the loop without constant manual intervention. However, I needed a workaround to make it happen since Power Automate wouldn't let me use a calculated column for filter queries.

The Problem with Calculated Columns in Power Automate

Here’s the snag: Power Automate doesn’t support Filter Queries with calculated columns, which meant I couldn’t just use the due date to trigger reminders. I spent about 15 minutes scratching my head over this, then decided to take a quick walk. That’s when it hit me—what if I copied the due date from the calculated column into a regular date column? I could then use this new column to filter my queries in Power Automate. Did I hear you say the Power of Taking a Walk?😄 YEA, YOU’RE RIGHT! Sometimes, a walk to refresh your brain really does spark the best ideas! And with that, the real work began.

Solution Overview: Copying Calculated Data to a New Column

As that idea hit me, I took a U-turn back home to my desk, running like I had just won the lottery😂! I decided to set up a new date column in my SharePoint List—let’s call it DueDate2. Then, I automated copying data from the calculated column to this new regular date column. This means that every time a new member is registered using the app, the data in the calculated due date column for the new member is copied to DueDate2 synchronously. Finally, I could use this new column in my Filter Query without any drama. Here is a step-by-step guide to how I was able to achieve that.

Step 1: Sign into Power Automate to create an automated cloud flow (Remember we want to automate this process)

Step 2: Name your flow and select a trigger, Here our trigger will be when an item is created in SharePoint because my data source is a SharePoint List. Go ahead and select Create

Step 3: Selecting the Site Address and SharePoint List you want the trigger to focus on

Once you've clicked on create, you will be directed here,
 click on when an item is created to insert your site address and select a list

Select your site address from the dropdown list or enter it manually

Once you've added your site address you
 should be able to see your list from the dropdown (Mine is gym-members)

Step 4: Adding an Action after it triggers

            After you've successfully configured the trigger, it's time to add an action, click on the plus (+) sign underneath when an item is created to add an action

Once you click on Add an Action, a page like this will pop out

Underneath Add an action, in the search bar just type in update then scroll down till you see SharePoint, then select Update Item as shown in the picture above.

After selecting Update Item, you will be directed to where to select your site address and the list name, just like we did in the previous step when setting up the trigger. (As shown in the image above)

Step 5: Selecting a Unique Identifier of the Column you want to update

After successfully entering your site address and selecting the list name, Scroll down to the box labeled ID to choose a unique identifier. Place your cursor inside the box labeled ID like you want to fill in some details, you will notice a blue button pops out in front of the box with two icons in it. (See Picture Below) then select the lightning icon.

Once you've selected the lightning icon, there will be a pop-up on the right, like the picture below, then choose ID. (See Picture Below )

Step 5: Selecting advanced parameters that copy the data from the calculated column to a regular column

After selecting ID from the dynamic content in the previous step, scroll down to the dropdown menu labeled Advance Parameters and select the regular date column you want the data from the calculated column to be copied to (mine is DueDate2) (See Picture Below)

Step 6: Updating the Regular Column with data from the Calculated Column

After Selecting the Regular Column from the previous step above, place your cursor inside the box of the regular column as if you want to fill in some details, just like in the picture above, you will notice a blue button pops up again with two icons like we did in Step 5, go ahead and select the lightning icon to pop out the dynamic content.

Once you've selected the lightning icon, there will be a pop-up on the right, like the picture below, then scroll down to choose the calculated column, you might need to click see more in case you don't see the calculated column in the list. (See Picture Below )

In my case, the calculated column is called DueDate (as shown below)

Once you've selected the calculated column, save your flow.

 To explain the last step: we set it up so that the DueDate2 column, which is a regular column, updates automatically with data from the calculated column.

Step 7: The final step is testing your flow to ensure everything works perfectly fine.

Conclusion

Developing this gym management app was a valuable lesson in navigating Power Automate’s quirks. Sometimes, finding a workaround is essential to get the job done—and in this case, it was definitely worth it. By creating a flow that copies calculated data into a regular, non-calculated column, we made it possible to use this data in Filter Queries within Power Automate. This approach isn’t limited to SharePoint Lists; it can be applied to other types of databases requiring a calculated column for filtering queries in Power Automate, making it a versatile solution for managing filter queries in Power Automate.