Filter Query to show last month

(0) Share

Report

Posted on by Gwill1983

271

Hi all,

I am setting up a report to run on the last day of each month to show all entries on an excel sheet from the current month.

For instance, On the 30th April 2022 I want to return all entries dated within April 2022.

I presume the simplest way to do this is to use the filter query option within the 'list rows present in a table' connector, but struggling to find a solution.

I have the rest of my flow to create the report all sorted, but it returns everything in the sheet with no filter.

If anybody can help then that would be appreciated

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (1)

fchopo 8,003 Moderator on at

Like (0)

Report
Hi @Gwill1983

You could do something like this:

1) Get start of current month:

startOfMonth(utcNow())

2) Add 1 month to current date

3) Get first day of next month, and substract a date (to get last day of month):

addDays(startOfMonth(body('Add_to_time')),-1)

4) List rows in excel using ISO 8601 format (important)

Hope it helps!

Ferran

Was this reply helpful? Yes No
Gwill1983 271 on at

Like (0)

Report

Thanks for the reply.

I have followed your steps and the flow fails with the error below:

Syntax error at position 76 in 'Date ge '2022-04-01T00:00:00.0000000Z' and le '2022-04-30T00:00:00.0000000Z''.
inner exception: Syntax error at position 76 in 'Date ge '2022-04-01T00:00:00.0000000Z' and le '2022-04-30T00:00:00.0000000Z''.
clientRequestId: 9a18f586-bce4-496c-a63b-ee4d5b9cb7f8

Any idea how to fix this?

Was this reply helpful? Yes No
Gwill1983 271 on at

Like (0)

Report

Can anybody help with this error?

Was this reply helpful? Yes No
Verified answer

v-yujincui-msft on at

Like (1)

Report
Hi @Gwill1983 ,

Based on the error message you provided, it seems to be a syntax error.

Currently, OData filter query operators are limited in Power Automate excel ‘List rows present in a table’ action. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.

Here is a link that might help you:
https://www.enjoysharepoint.com/power-automate-list-rows-present-in-a-table-filter-query/

In addition, maybe you could consider adding a Filter array Action.
@and(greaterOrEquals(item()?['Date'], outputs('Compose-start')),lessOrEquals(item()?['Date'], outputs('Compose-end')))

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
RobElliott 10,323 Super User 2025 Season 2 on at

Like (0)

Report

@Gwill1983 use ShipDate ge... and ShipDate le....

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report

@Gwill1983

As @RobElliott mentions, what's the name of your date column?

Was this reply helpful? Yes No
Gwill1983 271 on at

Like (0)

Report

Apologies. My Date column is named Date.

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report

Unfortunately, I didn't remember that ge and le operators are not allowed in OData filter query in Excel! So you should follow @v-yujincui-msft procedure.

Hope it helps!

Ferran

Was this reply helpful? Yes No
Gwill1983 271 on at

Like (0)

Report

Thanks for this.

It appears to work, but I dont have any data outside of this month currently so won't be able to confirm it until May!

I will feedback once I am able to.

Was this reply helpful? Yes No
biboy852 32 on at

Like (0)

Report

Hello, I've tried the flow above but seems like the filter array step didnt work as it doesn't show the output. Please help me on this and see the screenshot below.

Was this reply helpful? Yes No