You’re offline. This is a read only version of the page.
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?
OMG Manish,
I'm having a similar problem, but with SharePoint List as input. My issue is that I'm trying to send daily reminder emails to staff based on a calculated future task [ReminderDate]. Since I can't use ODATA to filter by calculated fieds, I try to use a Compose action using the array produced from the Get Items step. Since the Compose action's input is the array from Get Items, Compose is placed inside an Apply to each loop. The compose step just formats the Reminder Dates to 'MM/dd/yyyy'.
Here's the question: How do I use a Filter array to only capture and send emails where today's date is equal to formatted [Reminder Date].?
Great 👍
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! 😄
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']
😢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
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
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.
Share
Report
All responses (
Answers (
//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
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
Hi @ManishSolanki, sorry forgot to mention that it was already set to ISO8601 in my original post.
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
#1
WarrenBelz
146,745
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,091
Most Valuable Professional
