Filter help needed - "List Rows Present in a table"

[dominicgetdown]

Have a flow for "List Rows Present in a Table" -> "Apply to Each" -> "Get Items"

Need help with filtering the "List Rows Present in a Table" to reduce the item amount that the flow is searching through when it gets to "Get Items".  Have tried 2 days of google searching for this answer, but can't find something that works for me.

Have tried the 'Filter Query' in "List rows present" and creating a "Filter array (as seen in image)".

Does anyone know how to either, filter to "capture items only in the last X amount of days (like last 7 days)" or filter to only the most recent X amount of items (last 100 items or highest 100 IDs in the ID column)?

What the flow is doing is capturing items from an excel that is connected to a Microsoft Form.  The flow takes what is in the excel file and uses Get Items to transfer to a SharePoint list.  Want to use the filter to reduce the number of items being reviewed to make sure the limit is not reached (already have the limit set to max as well).

The second image attached shows my excel file with the date column and ID field.

Thanks for the help

  .

[VJR]

Hello @dominicgetdown 

What was it that you already tried with the Filter Query?

Were you able to do something like the one mentioned here

[dominicgetdown]

Tried that solution.  It was able to filter the items, but only captured items that were 7 days before and only 7 days before (Changed the -5 to -7).  Was hoping to get it to filter for "past 7 days" so essentially, "greater than or equal to 7 days before today".  I tried the 'ge' instead of 'eq', but it threw up the error in the image below.

Thanks

[VJR]

Hi @dominicgetdown 

Since Flow returns the numeric representation of a date you will need to convert it to an appropriate date.

Below video from @abm should definitely get you going to filter the date column.

https://www.youtube.com/watch?v=r1LhHU5lXkM

[dominicgetdown]

Will try it tomorrow and let you know.  Thank you.

[dominicgetdown]

From the video, followed the steps to create a filter

Getting this error from the image attached.

Using this expression below for the left side of the filter.  Then doing greater than or equal to 09/30/2021 (09/30/2021 on the right side of the filter)

addDays('1899-12-30', int(item()?['Date']), 'mm/dd/yyyy')

[VJR]

Hi @dominicgetdown 

I was able to filter the dates based on the instructions in the video.

Change the month to capitals in addDays('1899-12-30', int(item()?['Date']), 'MM/dd/yyyy')

• If still not working check the format of the Excel cell where the date is present (Right click -> Number format).  
  • Right click and check the current format of that cell. 
  • Then check whether it is showing the equivalent numeric value of that same date by going to the General tab.               

             

Press Cancel. We are just checking whether the numeric value is showing up, so do not save the General format.

• Time shouldn't matter but also check if the time portion 00:00:00 is causing the obstruction during the comparison in the Filter Array. Adding the time worked for me but it is worth to check whether something is wrong there.

Please share all your observations.

[v-LilyW-msft · Accepted solution]

Hi @dominicgetdown 

According to your description, you want to filter out rows whose date is the last 7 days.

Please take a try with the following screenshots to see if it helps.

This is my test excel

Flow in details:

Expression1:formatDateTime(item()?['Date'], 'yyyy-MM-dd')

Expression2:addDays(utcNow(), -7, 'yyyy-MM-dd')

After flow runs, flow will filter out rows whose date is the last 7 days.

Note: 'mm' means minute, 'MM' means month in Flow.

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.