Solved: Selecting most recent entry in Excel table

Selecting most recent entry in Excel table

I have a Microsoft form that acts as a self-assessment where users answer 10 questions on a 5-point scale, strongly disagree to strongly agree. I have an automated cloud flow that adds a new row to an Excel table (Table1) when a new form is submitted. Next, I want to list the rows present in the table I just added the form response to and select the most recent entry. I have a calculated column in the table that counts all “agree” and “strongly agree” responses when a new line is added. Eventually, my goal is to to build out a switch condition that sends one email to the respondent if the count of agree plus strongly agree is 1-5, a different email if the count is 6-8, and a different email if the count is 8-10.

The add a new row to an Excel table when a new form is submitted is operating just fine.

After the new form is submitted, I want to list the rows present in the table and grab the most recent response to use for further steps. To do this, I am using the filter query. My step looks like this:

I'm using the dynamic content for Responder's Email from the form submission to narrow the results to 1 user. However, if a user has submitted the form more than once, I want to pull their most recent submission by timestamp.

The “Timestamp” is a reference to the first column of my table, which is filled from the dynamic content from the form in the first step (add row to table).

Whenever when I run the flow, it returns the second most recent entry, not the first. I tried adding a delay (30 seconds, then 1 minute, then 2 minutes) before the list rows present in a table step, and sometimes it pulled the most recent entry, but it did not do so consistently.

From reading other posts, I am suspicious of the Timestamp. Here is how it is formatted:

Here is a look at the full table for reference:

And finally, here’s what the whole flow looks like so far. I am using the HTML table only to check my results from the list rows present in a table step. Once I can pull the correct entry, I will delete the HTML table and set up the Switch condition.

Can I use the ODATA filter in my list rows present in a table step to simply select the most recent entry based on email and timestamp? Is there a problem with the timestamp format?

Thank you for the response, Qi @v-qiaqi-msft!

This is working (listing the most recent response correctly), but what if 2 people respond to the form back-to-back? For example, person A submits the form, then 30 seconds later (while the flow is still in the 1 minute delay action) person B submits the form kicking off the flow again and adding person B's responses to the table. Person A would receive person B's responses because Person B's responses would be the newest timestamp. For this reason, I was hoping to include a unique identifier like the form submitter's email as a filter.

To test this, I reduced the "Delay" from 1 minute to 5 seconds. When I reduce the delay, the "List rows present in a table - after new row add" step is returning the second most recent entry again. It appears that the 1 minute delay is critical here.

Quick Links

Leaderboard > Power Automate - Building Flows