Unanswered

Filter Array with dates

(0) Share

Report

Posted on by MYGG

Hi guys.

The concept of my flow is very simple. It should take the rows with today approval date in my working Excel file and add rows in my main Excel file. (Assuming today is 6 Sep, so only company 456 should be filtered out)

The table in my working Excel file looks something like this.

CompanyName	ApprovalDate	ExpiryDate	ApplicationStatus	Update
123			Returned	1900-01-01
456	6-Sep-23	5-Sep-24	Approved	2023-09-07
789	24-Jul-23	23-Jul-24	Approved	2023-07-25
011	1-Jan-22	31-Dec-23	Approved	2022-01-02

I tried various method and decided to add a new column (Update) to be in the same format as Power Automate, hoping to make it easier. The formula I am using for the column is =DATE(YEAR(ApprovalDate),MONTH(ApprovalDate),DAY(ApprovalDate))+1

I know I am doing something wrong with the date, but I cannot proceed further. This is how my flow looks like.

When I run the flow, I am getting all the approved rows instead of just company 456. By right if the first filter array is working, I wouldn't even need the second filter array.

Help please? What should I do to rectify this?

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (1)

ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (1)

Report
Hi @MYGG

As utcNow() functions returns time along with date so to make it compatible with "Update" excel column, we will use formatdatetime() function in the expression:

Expressions are written in the expression box as highlighted in the above screenshot. Expression used for formatting date is:

formatDateTime(utcNow(),'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.

Thanks

Was this reply helpful? Yes No
MYGG 49 on at

Like (0)

Report

HI Manish

It doesn't work. It is still returning all the rows. and now, my 2nd filter array has an "apply to each" added to it automatically, and i ended up getting [] in the output.

output from the 1st filter array above. in my Excel file, this is in April 2022. so if the filter is working, this should have been filtered out.

output from the 2nd filter array. (I have thousands of rows in my working Excel, does this means that the flow only look at the first 256 rows?)

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

Like (0)

Report

Hi @MYGG

As Excel stores dates as integer so to get the date in ISO format, pls set the 'DateTime Format' parameter to "ISO 8601" in List rows present in a 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
MYGG 49 on at

Like (0)

Report

Hi @ManishSolanki, sorry forgot to mention that it was already set to ISO8601 in my original post.

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

Like (0)

Report

No problem. In that case, you can convert number to date and apply the filter action. Here is the nice article to convert number to dates:

How to get date from Excel as a date in Power Automate flow (tomriha.com)

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
MYGG 49 on at

Like (0)

Report
Hi @ManishSolanki , thank you and pardon the noob. I used the addDays and was able to convert the dates in my 'Update' column to the date format power automate recognise.

//for anyone who chance upon this in future, the expression I used is addDays('12/30/1899',int(items('Apply_to_each')?['Update']),'yyyy-MM-dd')

I have two questions then.
1) does it only do so for the first 256 rows? (I have thousands of row in my Excel and those with 'Update' equal will be at the end of the table.)
2) so how do I actually continue from here? how do i filter this 'Update' column equal to today and list out the whole row?

I tried filtering after the compose but I just get a blank output.

my results

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

Like (0)

Report

Hi @MYGG

I am glad that you are able to convert integer to date format 🙂

For 256 rows limit, you can increase it by enabling paging in the "Settings" of List rows present in a table action. Steps to update the paging setting:

1. Select the options elipses ("...")
2. Select 'Settings'

3. Enable Pagination
4. Set an appropriate row limit as per your need

As we have collected date in compose action so pass the output of that action in the filter array instead of original update column value (number):

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
MYGG 49 on at

Like (0)

Report

😢where am i doing it wrongly? it is supposed to add only the filtered rows into another table but instead it is adding the entire table from the working Excel file to the MasterDB.xlsx

my working file

the added rows into my main file

Was this reply helpful? Yes No
Verified answer

ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (1)

Report
Hi @MYGG

I have designed a sample flow that matches your requirements:

Input excel file:

Master Excel file:

After getting the records using "List rows present in a table", we will add "Filter array" to get records where update column show today's date:

Expression used for left hand operand:

addDays('12/30/1899',int(item()?['Update']),'yyyy-MM-dd')

Expression used for right hand operand:

formatDateTime(utcNow(),'yyyy-MM-dd')

Now, we will loop the records obtained by filter action and add those in the master excel file:

To set columns in target excel file, we need to write the expressions.

Expression used for COMAPANY_NAME:

item()?['Company Name']

basically, it takes as item()?['<<source excel column name>>'].

Similarly, expressions for "EXPIRY" & "APPROVAL" columns are respectively:

item()?['Expiry Date']

item()?['ApprovalDate']

Master excel file after running the flow:

I hope that this sample flow will help you in designing the flow you are looking for.

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
MYGG 49 on at

Like (2)

Report

OMG @ManishSolanki it works!

but not without some hiccup. I didnt notice I had a trailing space in the company name of my header and was wondering why the company names doesnt show up.

this whole experience has been fruitful. I had more understanding of compose, initalise variables and filter array actions.

thank you very much for your time and assistance! 😄

Was this reply helpful? Yes No