Create a list with short weather forecasts

[DriesV]

Hi,

I'm doing planning for a construction company which does constructions across western Europe.

I'd like to daily or weekly get an email with the short weather forecast for a list of cities/locations.

I was working on a flow that looks like this:

When I test I do get a message with something like: "Paris, Paris, France : Bring an umbrella. It's going to rain. The high will be 18."

But this only gives the forecast for one location. I'd like to get a list, for example:

• Amsterdam: .....
• Antwerp: ....
• Brussels: ....
• Paris: ....
• Bordeaux: ....
• Lyon: ....

I'd already be very happy with the base list per city; but in a completely beautiful situation I'd be able to weekly upload or paste a list of the locations we are performing constructions in that week with the corresponding construction project name and get tailored forecasts for them. For example:

• Jobsite Philips: forecast for Philips site in Berlin
• Jobsite Coca cola: forecast for Coca cola site in Ghent
• Jobsite Port of Rotterdam: forecast for Rotterdam
• ...

My infinite gratitude for any help with this!

[Expiscornovus · Accepted solution]

Hi @DriesV,

You could setup a list in SharePoint and use a Get Items action to retrieve your locations. Within an apply to each you could check the weather forecast for each location and append it to a Weeroverzicht string variable. You can use that variable in your e-mail action (outside of the apply to each loop).

Below is a screenshot of that approach.

There is an initialize variable action missing in this screenshot. Don't forget to add that one 🙂

Hope this helps to get you started?

[DriesV]

Hi Expiscornovus,

Your suggestion worked in the end to get me started! Thank you! Obviously I missed your mention "There is an initialize variable action missing in this screenshot. Don't forget to add that one "

so ended up googling until I found that one myself! 😅 It was only afterwards I saw that you had mentioned it.

I'm currently at the final phase of getting the full flow working perfectly; only one hiccup remaining:

I weekly automatically regenerate the file which holds the jobsites and locations. It just overwrites the old file.

But as the file is in effect always a new one, Power Automate seems to lose the connection to the file after each "regeneration". It's just an excel file which sits in a OneDrive location.

Can you maybe offer any help on that one too? I'm a bit lost trying to find a solution for the moment.

Thanks again!

[Expiscornovus]

Hi @DriesV,

Which action are you using to retrieve that file? And how have you configured that action?

[DriesV]

Hi Expiscornovus,

I'll translate my Dutch version to English.

Hereby the action: 

Create a list with rows in a table

Herewith the error message:

Error: Action "create a list with rows in a table" failed

Details: "No table with name "Tabel-Weersvoorspelling" has been found.

Maybe I have to modify the excel file generation, to have it generate a CSV file or something?

The main difficulty is that Power Automate only accepts data from within an excel table and not the data of the excel file that's not in a table. I had to write quite some VBA code to get my system to generate a table in excel, and not just an excel without tables in it, as it used to do.

Thanks again for the help!

[DriesV]

I'm at a loss. 

So using the "Get Items" command I did manage to get a beautiful result. I was able to get the weather forecast generated and using "Apply to each" I created an email which had Project number; Project name; Forecast, for each project.

But alas, the list of projects (with the project addresses inside) gets updated weekly or even multiple times a week. And the list is an automatically generated excel document. Apparently, Power Automate uses a unique file ID to connect to a file; so with every new generation, Power Automate can't find the file anymore (even though it's there, with the exact same name etc, just with a different ID)

So I used this guide (https://flowaltdelete.ca/2020/06/25/power-automate-excel-dynamic-file/comment-page-1/#comment-961) to automatically find the current file ID, Get tables, List rows present in a table.

But now when I come back to my original tool which was to get a weather report for every location in the list, I can't find a location variable anymore on which I can poll the weather report!

If I try to use Apply to each and then use "Value" from "List rows present in a table"; it polls MSN weather with a location variable such as {"@odata.etag":"","ItemInternalId":"4fcb84ff-b4d7-4f2a-a240-13e76750da51","Projnr":"21-501","ProjName":"Le Moulin Gris","Location":"75000 Paris"}, 

Which obviously MSN weather can't handle very well. I'm lost in this final step! I would need a way to set up an "Apply to each" command on which it only polls MSN Weather with the query "75000 Paris"; and after that "Append to string variable" the weather return along with the project number and name. 

So I'd do Append to string variable with something like:

</br><b>['Projnr'] ['ProjName'] : </b>['MSN weather summary']

so it adds the weather summary on a new line each time. I would end up emailing the string variable to the group of project leaders so they automatically have the weather report for their running projects and they can plan according to the predicted weather at their construction sites.

Any help would be greatly appreciated!!

[Expiscornovus]

Hi @DriesV,

I have one question to double check something. Is it a requirement that the Weervoorspelling planning Excel file gets overwritten every time. Or wouldn't you mind if your flow would generate a unique new file name every time the planning get's updated?

Btw, I am also Dutch. So, you don't have to translate all the screenshots 😉

[DriesV]

Hi Expiscornovus,

I wouldn't mind that at al.

I've just also had the idea of changing things around: Now I'm generating the planning excel file using VBA, based on some other excel file.

I was just thinking: instead of overwriting the file every time; maybe I can just update the table contents in the (previously) generated file. That way the excel file ID would probably remain the same??

But that would require me to rewrite my VBA code and do some research therein. VBA isn't really my specialty...

[DriesV]

👋 🙂 @Jcook ‌‌

P.S. you can remove my replies on your blog if you feel my case is polluting the comment tree on your fabulous blog post too much 😉 We can continue the conversation here just as well.