Filtering table using today's date - issue with integers

[Hugo-Aegon]

Hi,

I have a power automate flow that uses Get Table / List rows present in a table to retrieve a table from SharePoint which contains approx. 3,000 rows. This table contains a date field [Trade Date]. I want toceate an HTML table that only shows those rows where [Trade Date] = Todays Date but I am encountering several issues:

1. The Get Table returns the Trade Date value as an integer e.g. 45484 for 2024-07-11. I managed to convert Today's Date to an integer but it returns 45,484 so the filter doesn't work.

2. Instead I used Compose to return Todays Date using formatDateTime(utcNow(), 'yyyy-MM-dd'). I then tried to use the Filter Query to restrict the table to only those rows where the formatted Trade Date equals the Todays Date from Compose but this didn't seem to work either.

3. I then tried using Filter Array, where I use the formatted Todays Date function above to compare to the Compose action output.  When I try to use the Filter Array output as the input for the Select action it doesn't work as it seems the Filter Array is producing an empty output, only when I use the List tables output but then I think it is skipping the Filter Array step completely.

I feel this is an easy problem to solve but I cannot get my head around how to move forward - whenever I get the Trade Date and Todays Date to be in the same format so that they can match, it never seems to actually work in restricting the data. I also want to avoid iterating through each row as this feels very clumsy and doesn't produce consistent results.

As a little bonus, I also have a field [Settlement Date] that contains date values (again picked up as integers) and a string 'TBC'. I want to use the same formatting I use above for the Trade Date but this doesn't work when it encounters a string value. I've tried using if(equals(()) but this didn't work either.

Any help would be greatly appreciated!

Thanks

[creativeopinion · Accepted solution]

@Hugo-Aegon When working with Dates/Times in Excel you need to ensure that you set the DateTime Format as ISO8601 otherwise the output of the dates from Excel will be in serial format.

New Designer

In the New Designer click on Show All then scroll to the bottom and select ISO8601 as the DateTime format.

Classic Designer

In the Classic Designer click on Show Advanced options and select ISO8601 as the DateTime format.

You can use the Filter Query field to define a single condition to filter your rows by. If you need to filter by more than one condition you will need to use a Filter Array action. I've linked a few tutorials you might be interested in below.

If you still run into issues with your flow, ensure you've toggled off the New Designer, click on each action to expand and upload a screenshot of your full flow in edit mode.

How to DYNAMICALLY Select a Table in the List Rows Present in a Table Power Automate Action

In this Microsoft Power Automate tutorial, I’ll show you how to dynamically set the File and Table field.

ALSO IN THIS VIDEO:

✅ How to create an Automated Cloud Flow

✅ How to dynamically select a Table in the List Rows Present in a Table Action

✅ How to use the Parse JSON action

✅ How to use the When a file is created (properties only) trigger

✅ How to avoid the Apply to Each Action

✅ How to Create an Item in SharePoint from Excel Data

----

How to Work with 📆 Dates in Power Automate | Example Scenarios and Tips & Tricks

Are you easily stumped when working with Dates in Power Automate? In this Power Automate tutorial, I’ll show you how to compose an expression that will return a future date, a past date, how to count the number of days between two dates, how to check for a birthdate and anniversary date as well as tips and tricks when working with dates in Power Automate.

I’ll cover some common use cases and concepts that can help you to build better Power Automate flows. Feel free to skip ahead using the timestamps listed below. I’ve also linked a few other tutorials that you might be interested in as well.

IN THIS VIDEO:

✅ 4 Date Functions You Need to Know

✅ How to use the Convert Time Zone Action

✅ How to Get a Future Date

✅ How to Get a Past Date

✅ How to Return SharePoint Items Due in a Number of Days

✅ How to Return SharePoint Items Due within a Date Range

✅ How to write a Filter Query for SharePoint Items Due in 30, 60 and 90 Days

✅ How to Calculate the Number of Days between Two Dates

✅ How to Check for a Birthdate and Anniversary Date

✅ How to work with Dates and Times in Excel

✅ How to use a Manual Trigger with Date Input

✅ How to Output Dynamic Text with an Expression

✅ How to Check if a Date Falls on a Weekend

---

Are you using the Microsoft Power Automate Filter Array Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

---

5 Power Automate Troubleshooting FAQs and Helpful Tips for Creating Better Flows

In this Power Automate tutorial, I explore 5 frequently asked questions that pop up when troubleshooting a flow. If you’d like to to level up your Power Automate flow skills and learn how to troubleshoot your Power Automate flow—this tutorial is for you!

IN THIS VIDEO:

✅ How to troubleshoot a false Condition action result

✅ How to get dynamic content when it isn’t selectable from the list of dynamic content

✅ How to troubleshoot an Apply to Each action that isn’t looping through

✅ How to troubleshoot a skipped Apply to Each action

✅ How to troubleshoot a Filter Query

✅ How to use a SharePoint yes/no column in a Filter Query

✅ How to use Compose actions to troubleshoot a Power Automate flow

✅ How to troubleshoot multiple emails being sent

✅ How to troubleshoot multiple Teams messages being sent

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[Hugo-Aegon]

Hi creativeopinion,

These were helpful thank you. I never managed to get the Filter Query within List Rows to work - it never seemed to pick up a column name, even after removing spaces, using quotes, dynamic values or static strings.

However, the filter array option worked, after quite a bit of tuning, I must admit!!