I have an excel file with key columns and also 365 columns with dates as header in different columns for whole year, My requirement is to read key columns and also the date column which equals to current day only and send email with content of that particular date columns
For example when Power Automate workflow runs on 10th Jan, it should read the date under the column - 10th Jan only . Not other columns like 9th or 11th Jan. Next day it should read 11th Jan and so on.
Is it possible to automate such scenarios in power automate?. How can I build flow to read different columns in excel depending on the date it is triggered?
Yes, thank you.
Just tested right after replying to the thread, it does not pick up data outside table.
If that extra information is not part of the actual Table, then it won't include that information when it brings in the data to Power Automate.
Thanks a lot, it's reading the columns dynamically now.
I am using the Table from excel to read, so above solution worked perfectly.
Also, how we can filter out certain rows, in case the excel contains some title details above the actual table data like in the example attached.
Unwanted Rows
Hopefully this will get what you're looking for. Note that I had to convert your Excel data to an Excel Table. You can then reapply the formatting to the Table as required. Let me know if this is not possible to set as a Table but would definitely make it harder. Also, note that I didn't add anything relating to sending emails - just focused on getting the appropriate data from Excel.
For this example, I'm using the following Excel Table (only includes a few dates for testing purposes).
See full flow below. I'll go into each of the actions.
Recurrence is set to run once a day. Note sure what you have here.
List rows present in a table will retrieve all the data from your Excel Table. We won't be able to filter the data at this point.
Filter array then filters on the rows where the current date column is equal to No (reports that haven't been completed for the current date). The expression used is below. It retrieves the value from the column titled the current date (Jun-21 for example).
item()?[formatDateTime(utcNow(), 'MMM-dd')]
Select uses the output from Filter array and only maps the relevant fields we want. The expressions are below:
item()?['ID']
item()?['Report']
item()?['Frequency']
item()?['Due on']
item()?['1st Owner']
After running the flow, we would get only the items where today's date value is No. We could then loop through each of these items and send an email with the relevant data.
[
{
"ID": "2",
"Report": "Report 2",
"Frequency": "Daily",
"Due on": "Daily",
"1st Owner": "A1"
},
{
"ID": "4",
"Report": "Report 4",
"Frequency": "Daily",
"Due on": "Daily",
"1st Owner": "A1"
}
]
----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.
Hello,
Thanks for the reply. Attached the sample screenshot.
Basically, the idea is to send the mail form power automate to Report owners if their Report is not refreshed as a reminder at the end of the day.
I do not need all the columns. The Mail need to state what report is not refreshed along with frequency.
Are you able to show a screenshot of some column headers within your Excel file? Definitely possible - just wanted to know the exact format and whether it's consistent across all your column headers.
Also did you want all the rows from the Excel file? And what did you want to do with the data?