Filter Query to show last month

[Gwill1983]

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

[fchopo]

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

[Gwill1983]

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?

[Gwill1983]

Can anybody help with this error?

[v-yujincui-msft · Accepted solution]

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

[RobElliott]

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

[fchopo]

@Gwill1983 

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

[Gwill1983]

Apologies. My Date column is named Date.

[fchopo]

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

[Gwill1983]

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.

[biboy852]

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.