The new “Populate a Microsoft Word template” action in Flow that was released last week got me thinking: apart from the obvious mail-merge applications, this could be used to build reports in Word! Here’s a very simple example.
Let’s say you have a table in an Excel worksheet stored in OneDrive for Business:
Let’s say you have a Word document that looks like this:
Shown in this screenshot are two Plain Text Content Controls called ProductName and SalesAmount, where text can be entered and with placeholder text already added. The screenshot above is with the document in Design Mode with the controls visible; the document would normally look like this:
The idea is to loop through the rows in the Excel table and use the data on each row to populate the content controls in the template and then create a new Word document. Here’s a Flow that does this:
Looking at some of these actions in a bit more detail:
- List rows present in a table gets the data from the Excel table shown above
- Apply to each loops over each row in the table
- Populate a Microsoft Word Template takes the Word document shown above and then puts the value from the Product column in the current row from Excel into the ProductName content control, and the value from the Sales column in the current row into the SalesAmount content control:
- Create file saves the resulting new Word document in a separate folder:
Here are the three Word files that are created:
And here’s what the contents of AppleSales.docx looks like:
This is only a very, very basic example of what’s possible: you could convert these documents to pdf, email them out as attachments, and even in the future get your source data direct from Power BI.
Why would you want to build reports in Word though? Some reports are very text heavy, and although you can do text substitution in Power BI (for example using the Enlighten Data Story visual), nothing does highly-formatted text as well as Word. OK, it’s not going to be something that you do very often – the vast majority of reports you build will be better off in Power BI, as paginated reports/SSRS, or in Excel – but there are definitely some use cases. The new Flow action is very limited right now but with support for more types of content control even more sophisticated reports will be possible using this technique.
Word Reports !! – Wow – and I thought word for writing poetry and for everything else there was Excel
Thanks for this Chris – very handy. You may want to know that Word Online (Business) is a Premium Connector and therefore is only accessible through Flow Plan 1 (USD5.00/user/month) or Flow Plan 2 (USD15.00/user/month) but not through the Free plan or Flow for Office 365 plan.
https://emea.flow.microsoft.com/en-us/pricing/
I’ll leave others to consider whether connecting to Word should have been made available as part of the Office 365 plans.
Is there a way i can put all those three excel rows into one word document instead of three different word documents.
this is great, but there’s one problem that I am running into with doing something very similar using Microsoft Excel as a template.
If multiple people run the flow at the same time, and especially for reports with lots of rose, the data can get mixed up in the template.
Is there a way for flow to check out the document so that it does not get edited by other flows? And is there a graceful error handling when a flow runs while a document is already checked out?