How to Filter Excel Files Based On Date Using Power Automate + Send an Email

Last week, I worked on a Power Automate flow that required me to send emails to Account Managers using data from an Excel file stored in OneDrive. The goal was to filter out records with a ‘Scheduled Delivery Date’ for the next three months and then match the records to the correct ‘Account Manager Name.’ After filtering, the Excel spreadsheet needed to email each account manager their relevant data.

In this Power Automata tutorial, I will tell you how to filter Excel files based on date using Power Automate. Also, we will see how to send the filter data to the corresponding email.

Filter Excel Files Based on Date and Send an Email Using Power Automate

Before I proceed with the flow, ensure you have an Excel file and that the Excel file is formatted as a table. In this example, I will use the Excel file shown below:

I store this Excel file in OneDrive.

Let’s follow the below steps:

1. Navigate to the Power Automate Home page, click + Create, select the Instant cloud flow, provide the flow name, choose the flow trigger (i.e., Manually trigger a flow), and click the Create button.

2. Now I need to select the Excel file that is presented in OneDrive, so I used List rows present in a table action and provided the below parameters:

• Location: Select the OneDrive for Business from the drop-down.
• Document Library: Select the Document Library from the drop-down.
• File: Choose the Excel file in the OneDrive.
• Table: Select your table name from the drop-down (if nothing is present, it does not show).
• DateTime Format: Select ISO 8601 from the drop-down.

3. I need to filter the Excel for the next three months, so I use the Filter array action. If your requirement equals some date or column value, you can use List rows present in a table filter query. I used the Filter array action because the List rows in a table filter query don’t support less than or greater than.

Then provide the below parameters:

• From: Provide the dynamic content body/value from List rows in a table.
• Filter Query: Then provide the below expression:

@lessOrEquals(@{formatDateTime(item()?['Scheduled Delivery Date'], 'yyyy-MM-dd')},@{addToTime(utcNow(), 3, 'Month', 'yyyy-MM-dd')})

The expression checks if the “Scheduled Delivery Date” is less than or equal to a date three months from today.

Then, I run the flow. As you can see, our filter array ran successfully, giving the filter array in the body section. Now we copy the json put any text file.

4. Then edit the flow and Add a Parse JSON action. Also, provide the below parameter:

• Schema: click Use Sample payload to generate schema, past the ‘Filter array’ action output, then click Done.
• Content: Provide the dynamic content output of the Filter array action.

I need to send the filtered Excel data corresponding to the Account Manager. I only want to send one if the same email appears more than once. If your requirements are the same, follow the steps below:

Now, I need to select the email using the ‘Select’ action with the following parameters:

• From: Provide the dynamic content Body from Parse JSON
• Map: Provide the dynamic content Body Email Address from Parse JSON

Now, if you run this flow, you might get repeated emails. But I only need unique emails, so I added a Compose action with the following expression:

union(body('Select'),body('Select'))

Here, I need to send an email, so I add an Apply to each action and provide the output of the compose action.

Then, inside the ‘Apply to each‘ action, I added another ‘Filter array’ action to filter the data based on the email.

• From: Provide the dynamic content Body from Parse JSON
• Filter Query: dynamic content Body Email Address is equal to Current item from apply to each

Now, this ‘Filter array’ action provides data based on the email. After that, I select all the data using the ‘Select’ action. Check the screenshot below.

Then, I used the ‘Create HTML table‘ action and provided the output from the ‘Select’ action.

Finally, I added a Send an Email (V2) action Set the required parameters:

• To: Select the Current item from dynamic content under apply to each.
• Subject: Provide a subject line for a mail.
• Body: Provide a detailed description of a mail with the Create HTML table output.

Now that the flow is ready, we will run it.

Run the Flow to Filter Excel Files Based on Date and Send an Email to the Corresponding Email

Now, we test our flow to do this. Click on Test Select Manually -> Click on Test inside the Test Flow tab.

After the flow runs successfully, each account manager receives an email with the details they need about the upcoming deliveries based on the filtered records. Check the screenshot below.

I hope you follow all the steps to filter Excel files by date and send an email to the corresponding email address in Power Automate.

Also, you may like some more Power Automate tutorials:

• Send Birthday Emails from Excel using Power Automate
• Check If a row exists in Excel using Power Automate
• Remove Top Few Lines of an Excel File Using Power Automate
• Send a No Reply Email from a Specific Sender Name in Power Automate
• Overwrite Files Using Create File Action in Power Automate

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.