You’re offline. This is a read only version of the page.
Hello
I'm new to Power Automate and am trying to build a flow that sends an email to me 30 days before a date in a column in an excel file setting out the content of that row. The file has a list of a bunch of sites with an expiry date for a certain thing - the email is designed to be a reminder.
Based on reading suggestions from elsewhere, so far I think I need to use:
However, I get lost around the Create HTML table, as PA starts to do odd things (like repeatedly inserting 'Apply to each' stages). I've tried various things, and managed to get it to send emails, but either they just list every site (and the date is garbled as 44290 or something), appear as a complete mess, or are blank.
Would anyone be able to explain how such a flow should be laid out? Many thanks in advance!
Thank you - it finally works! Your help (and @MarvinBangert ) is really appreciated!!
Hi @JW_TH ,
I have a test for your reference, you could refer to the screenshot:
Excel table:
Flow:
JSON:
Share
Report
All responses (
Answers ({
"type": "array",
"items": {
"type": "object",
"properties": {
"@@odata.etag": {
"type": "string"
},
"ItemInternalId": {
"type": "string"
},
"Site": {
"type": "string"
},
"Expiry date": {
"type": "string"
}
},
"required": [
"@@odata.etag",
"ItemInternalId",
"Site",
"Expiry date"
]
}
}Expression:
addDays(utcNow(), 30)
formatDateTime(item()['Expiry date'],'dd-MM-yyyy')
Best Regards,
Jessica Gu
Hi @v-JessicaG-msft and sorry for the delay in responding. Please see below for a screenshot of the flow, and the output in the email. As you can see, the email output is just a list of all the expiry dates in the file, and does not only list the ones coming up in the next 30 days (and there are no site names!).
Hi @JW_TH ,
Could you show me a screenshot of the your flow? Provide more information to help me solve your issue.
Best Regards,
Jessica Gu
Thanks again for the response. Unfortunately neither of these solutions worked. In both cases the outputs I got were every date, regardless of whether it was more or less than 30 days ahead. Plus I also seemed to be unable to add the site column to the output table.
Hi @JW_TH ,
1.You could use this expression " addDAYS('1899-12-30', int(outputs('Compose')),'dd-MM-yyyy') " to convert dates.
2.You could also try to select ISO 8601 for DateTime format when getting rows in table excel:
Please refer to this link: https://powerusers.microsoft.com/t5/General-Power-Automate/Manipulating-Flow-Excel-Date/td-p/365876
Best Regards,
Jessica Gu
Thanks @MarvinBangert - that has worked exactly as suggested - thank you!
If anyone has any idea about the dates issue, do let me know.
Hey @JW_TH
To your first question: Please add a "Parse JSON" action, the "Content" is the Body from "Filter array", you can generate the "Schema" by using an old run, getting the body from "Filter array", click within the "Parse JSON" on "Generate from sample", paste the copied body from "Filter array" and submit. Then try again using "Custom" Columns within "Create HTML table". Please notice to change your "From" Body also to the "Parse JSON" action. The parse will let you select the values within dynamic values and it shouldn't create an "apply to each" anymore:
Does this help you? Otherwise please give me some more information.
Best regards
Marvin
If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.
Blog: Cloudkumpel
Thank you very much for your help. This has worked - however, still a couple of issues/questions:
1. I don't want the first two column (@odata.etag and ItemInternalID) to display in the email (I don't know where they come from, as they are not in the original file) - how do I get rid of them? If I try to change the columns displayed in the 'Create HTML table' by going to the 'Custom' bit, PA automatically inserts an 'Apply to all' level. I would like to be able to customise which columns display (as in the file I will eventually use there are a lot, but I only want site and expiry date).
2. Why was the workaround needed? Does PA not recognise dd/mm date formats, or does any type of date not get properly transferred into an html table ? I don't really want to have a column with manually inputted dates as it relies on people inputting them correctly when updating the column (whereas having the date format set would automatically adjust any errors) - for example, I changed one date from 09/02/2022 to 9/2/2022 and it didn't get picked up when I tested again.
Hi @JW_TH ,
You could change the format of the date column in excel to text, so that the date can be displayed correctly in the Create HTML table.
I have test for your reference, please try the following workaround:
Best Regards,
Jessica Gu
#1
Michael E. Gernaey
497
Super User 2025 Season 2
#2
David_MA
436
Super User 2025 Season 2
#3
Riyaz_riz11
244
Super User 2025 Season 2
