cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dominicgetdown
New Member

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

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

  Capture.PNGCapture1.PNG.

1 ACCEPTED SOLUTION

Accepted Solutions

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

vLilyWmsft_0-1633492084581.png

Flow in details:

vLilyWmsft_1-1633492145680.png

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

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

vLilyWmsft_6-1633492854333.png

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

vLilyWmsft_5-1633492438463.png

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

vLilyWmsft_7-1633492974619.png

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.

View solution in original post

7 REPLIES 7
VJR
Memorable Member
Memorable Member

Hello @dominicgetdown 

 

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

Were you able to do something like the one mentioned here

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

 

Capture3.PNG

VJR
Memorable Member
Memorable Member

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

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

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')

 

Capture (1).PNG

VJR
Memorable Member
Memorable Member

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.VJR_0-1633490658804.png

       

       
    • Then check whether it is showing the equivalent numeric value of that same date by going to the General tab.               

             

             VJR_2-1633490865778.png

 

 

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.

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

vLilyWmsft_0-1633492084581.png

Flow in details:

vLilyWmsft_1-1633492145680.png

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

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

vLilyWmsft_6-1633492854333.png

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

vLilyWmsft_5-1633492438463.png

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

vLilyWmsft_7-1633492974619.png

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.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,246)