Filter Query on List Rows from Excel table

Like (0) Share

Report

Posted on 18 Oct 2023 10:35:57 by Jap11

331

Hello,

Hoping someone is able to help!

I want to get any rows that have a date from the past 14 days, so I'm using the 'List rows present in a table', then using the filter query to retrieve the rows I want. However I'm not able to get the flow to work, I'm guessing there is something obvious I'm doing wrong but still a beginner!

I've used this exact filter query for 'get items' in a sharepoint list and it has worked, but I'm guessing I can't just write 'Date of Incident' and assume that will know which column to lookup to in my excel sheet. or should it? Is there a way I should get the dynamic content to pull through a step earlier so that I can use that for 'Date of Incident'... i'm not sure!

Anyway, if anyone would be able to help or know the correct way of doing this, it would be greatly appreciated!

This is the full expression from my filter query, the date is formatted the same as in Excel:

Date of Incident ge '@{formatDateTime(addDays(utcNow(), -15), 'dd-MM-yyyy')}'

Thanks! 🙂

All responses (12)

Answers (2)

pakalolo13 64 on 21 Feb 2024 at 16:00:58

Like (0)

Report

Re: Filter Query on List Rows from Excel table

The flow still returns all items in the list.
In the List rows present in a table section, I do have the DateTime Format selected as ISO 8601.
ManishSolanki 15,085 Super User 2025 Season 1 on 21 Feb 2024 at 15:53:32

Like (0)

Report
Re: Filter Query on List Rows from Excel table

Hi @pakalolo13

Pls try the below expression:

@greaterOrEquals(formatDatetime(item()?['DateOfOperation'],'yyyy-MM-dd'), addDays(utcNow(), -1, 'yyyy-MM-dd'))

pls replace the column name with actual name in the excel sheet in above expression.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
pakalolo13 64 on 21 Feb 2024 at 15:44:54

Like (0)

Report

Re: Filter Query on List Rows from Excel table

It is date time value. So if there are no records which match the filter, I should be returning no results, correct? My flow is always returning all the items in the table every time it runs. Don't understand what I am missing. I have seen others with the same formula work as intended. All help is appreciated.
ManishSolanki 15,085 Super User 2025 Season 1 on 21 Feb 2024 at 02:54:41

Like (0)

Report
Re: Filter Query on List Rows from Excel table

Hi @pakalolo13

Pls verify if 'list rows present in a table' action is returning datetime or integer value for 'Date' column by looking at the output from previous run flow instance. If its date, then pls try the below expression:

@greaterOrEquals(item()?['Date'], addDays(utcNow(), -1, 'yyyy-MM-dd'))

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
pakalolo13 64 on 21 Feb 2024 at 01:09:30

Like (0)

Report

Re: Filter Query on List Rows from Excel table

I know this is an old post but I have used this exact filter query and mine is still producing too many results. I have changed from -15 to -1 as shown below.
@greaterOrEquals(item()?['Date'], formatDateTime(addDays(utcNow(), -1), 'dd-MM-yyyy'))
I should only see one record as of today 2/20/2024. Any help is greatly appreciated!
Date
2023-03-27
2023-03-27
2023-11-07
2023-11-08
2023-11-08
2024-02-16
2024-02-20
Verified answer

grantjenkins 11,059 Super User 2025 Season 1 on 24 Oct 2023 at 14:04:53

Like (1)

Report

Re: Filter Query on List Rows from Excel table

I'm not sure why your Filter query isn't working. I'd confirm your column name is Site and the text 'Location A' in your Excel table doesn't have any spaces at the start/end that might cause it to not be equal. On a side note - you mentioned this: Site eq 'Location A', it seems to totally ignore the filter array and pulls through every single row that has Location A. The query you put will apply a filter to return all rows that has Location A - is that not what you want?

For the Filter array, I believe your column name in your Excel Table is 'Date of Incident' not "DateOfIncident'. You just need to make sure it's exactly the same as what comes through in your List rows present in a table action. To check you can click on the download link after you run the flow.
Jap11 331 on 23 Oct 2023 at 14:18:25

Like (0)

Report

Re: Filter Query on List Rows from Excel table

@grantjenkins - thank you, that worked! Really appreciate your help.

I'm just wondering if you may know how to solve this issue as well... when I use a filter query on 'list rows present in a table', for example 'Site' like this: Site eq 'Location A', it seems to totally ignore the filter array and pulls through every single row that has Location A.

Do you know why it could be doing this? For info, this is the filter array another user suggested and did work until I put in the filter query on the 'List rows present in a table Data' action.

@greaterOrEquals(item()?['DateOfIncident'], formatDateTime(addDays(utcNow(), -15), 'dd-MM-yyyy'))

UPDATE: @grantjenkins - I noticed that actually the filter array is ignored regardless of the the filter query, it seems to always show show all rows.

Thanks,
Josh
Verified answer

grantjenkins 11,059 Super User 2025 Season 1 on 23 Oct 2023 at 12:48:35

Like (1)

Report
Re: Filter Query on List Rows from Excel table

I'd remove both Apply to each actions then add your Create HTML table using the following.

From: Body from your Filter array

Fields:

//Date of incident item()?['Date of Incident'] //Days since incident item()?['Days since Incident'] //Other fields... item()?['NAME OF YOUR FIELD']
Jap11 331 on 18 Oct 2023 at 14:14:57

Like (0)

Report

Re: Filter Query on List Rows from Excel table

Hi @ManishSolanki This solved the problem, thank you so much again.

Sorry to ask for further assistance but now I've spotted an issue this causes to my flow. Originally, I was putting the value from 'list rows present in table' into 'create html table', so that I could add these outputs into an email, however this is pulling through all rows still.

I realise this is because I'm selecting the value from 'list rows present in table', and not the filter array that actually has the rows I need.

Is there a way to present the rows retrieved from the filter array into an email? preferably similar to what I have already done. When I have tried doing it, it forces an apply to each twice, and I'm guessing this is not going to work.

Thank you again,
Josh
ManishSolanki 15,085 Super User 2025 Season 1 on 18 Oct 2023 at 13:45:02

Like (1)

Report

Re: Filter Query on List Rows from Excel table

Hi @jap11

Pls set the "DateTime Format" flag to "ISO 8601" in List rows present in table action:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks