Answered

Filter excel table with Date and time column to fetch previous month Data

(0) Share

Report

Posted on by vrevankar

Hi Makers,

I have created scheduled flow which triggers on the 1st of every month to filter data for previous month from the excel table based on completion time column which is of date and time. built flow as below and tried several methods avaible on web but not able to filter the data . belwo is the screen shot of the flow please help in achiveinging the same.

The current flow is not working with this method

Categories:

General topics

I have the same question (0)

All responses (7)

Answers (1)

v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @vrevankar ,

What is the result of List Rows? Empty?

If there are a lot of rows in your data source but few that match the criteria, I recommend that you turn on the pagination settings and test again.

Best regards,

Rimmon

Was this reply helpful? Yes No
vrevankar 94 on at

Like (0)

Report
Copy link

Link copied!

@v-mengmli-msft Sir thank you for quick response the pagination is turned on and set the limit to 5000 but still getting the error as below
not sure why is this taking following input as filter query
"CompletionTime gt '2024-03-01T00:00:00.0000000Z' and CompletionTime le '2024-02-29T00:00:00.0000000Z'\n",

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @vrevankar ,

The error shows gt and le can't be in filter query at the same time.

I recommend you use gt in filter query and put le in "Filter array" action.

Best regards,

Rimmon

Was this reply helpful? Yes No
vrevankar 94 on at

Like (0)

Report
Copy link

Link copied!

@v-mengmli-msft tried with the above steps seems the flow is still failing stating its not able to find the column named Completion time in excel Table but the table exists as show below screen shot

please suggest to move forward below is the error screen shot

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @vrevankar ,

The filter query doesn't support column name with spaces as well.

Two ways for you reference.

1/Delete spaces in column name.

2/Use Filter array action again.

Best regards,

Rimmon

Was this reply helpful? Yes No
vrevankar 94 on at

Like (0)

Report
Copy link

Link copied!

@v-mengmli-msft Thanks for the quick response sir now the flow runs perfect but post filtering the data in List rows and Filter array im trying to create a CSV Table to send the filtered data . once the flow runs succesfully the CSV table shows blank in email attchement .
Recent scucces screen shot

Was this reply helpful? Yes No
Verified answer

v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @vrevankar ,

I recommend you create Html table instead of CSV table if you are inserting table in email body. This is because email body expects html format.

Best regards,

Rimmon

Was this reply helpful? Yes No