Power Platform Community Forum Thread Details

Hello Power Platform community,

I am a very beginner and would like to get help guidance on building a Power Automate flow that runs once per month and processes a stored Excel Sharepoint. Below is the exact behaviour I want to implement and some specific questions where I need help.

The flow runs on the 1st day of every month.
The flow reads an Excel file stored in Sharepoint (the file is updated manually once per month before the 1st).
For each booking row in the table, process only those rows that meet all of these:
Status = Provisional
Arrival Date is on run date (the 1st of the month)
Arrival Date is on run date + 6 weeks (42 days)
For each row that meets the above:
1. If Contact Email is empty: add the booking row to Missing Email Report (an Excel table). Do not send any email.
2. If Contact Email exists: send an automated reminder email to the Contact Email.
3. After the send attempt, always append a record to Sent Email Report (an Excel table) with at least these fields: Event ID, Contact Email, SentDate (utcNow), Status, SendResult (Success/Failure), and optionally ErrorMessage.
4. If the email action fails, send an error notification to internal admins

TEST_provisional ...

Categories:

Building flows

Power Automate Mobile App

Using connectors

Power Automate Desktop

Process mining

Translation quality feedback

Connector development

Using flows

General topics

I can help you get started on this, but there is too much here to go over in one go and I don't have time to build a flow to do everything you have asked. To get started, you would do this:

Create a flow that is scheduled and configure it to run on the first day of the month.
Then use the List rows present in a table action with the filter Status eq 'provisional'
1. You'll want to remove the spaces in the header row of the spreadsheet for the filter query
2. The filter query is case sensitive
3. The spreadsheet must contain a defined table
4. Ensure you go to advanced options and set the date time format to ISO 8601
Add a condition and use the length expression to make sure at least one item is found.
1. Use the expression length(outputs('List_rows_present_in_a_table')?['body/value']) and set the condition is greater than 0
On the yes side of the condition, add a filter array action.
1. In From, insert the value from the Excel action in the dynamic content
2. Put the filter in advanced mode and use this filter: @and(greaterOrEquals(item()?['ArrivalDate'], startOfDay(utcNow())), less(item()?['ArrivalDate'], addDays(startOfDay(utcNow()), 1)))
  1. The above filter is only 1/2 of what you need. The easiest thing to do this would be to use two filter actions and process each of your requirements separately or update the above to get items where the arrival date is the current date or the current date plus 42 days.
3. To reference data, you'll need to use expression such as this to send the e-mail: items('Apply_to_each_Arrival_Date_Today')?['ArrivalDate']

The flow will look like this except you will use a scheduled trigger. I recommend starting by setting it up with a manual trigger and change it once you have it working.