Filter Query on List Rows from Excel table

(0) Share

Report

Posted on by Jap11

386

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! 🙂

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (2)

ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (1)

Report
Hi @jap11

I would suggest use "Filter array" action after "List rows present in a table" action:

Filter query used here:

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

The out of filter array will give the required records.

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

Thanks

Was this reply helpful? Yes No
Jap11 386 on at

Like (0)

Report

Hi @ManishSolanki - this has fixed my flow to actually run now, which is great, thank you!

There seems to be one other issue now though which hopefully you might be able to help me with as well:

The date is formatted like this in my excel spreadsheet:

However it's coming through like this:

This is causing my flow to pull every record, rather than just the past 15 days.

Any ideas?

Thank you again!

Was this reply helpful? Yes No
ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Jap11 386 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (1)

Report
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']

Was this reply helpful? Yes No
Jap11 386 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (1)

Report

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.

Was this reply helpful? Yes No
pakalolo13 66 on at

Like (0)

Report

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

Was this reply helpful? Yes No
ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (0)

Report
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.

Was this reply helpful? Yes No
pakalolo13 66 on at

Like (0)

Report

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.

Was this reply helpful? Yes No