Unanswered

Populate a Word Document Template with Data from Excel (multiple rows to one output docx file)

(0) Share

Report

Posted on by cepaterson

I am trying to create a flow that takes data from a file saved in Sharepoint (I've tried both a spreadsheet and List in my many attempts) and then populates a word document - this is to generate invoices.

The trigger is manual and the flow initiated by a task being completed in Teams.

The data in the Excel is structured (represented in the below image which hopefully makes sense) in such a way that I need to return multiple rows from excel to populate a single table/generate a single document in word.

As noted in the colour key below:

Green = my notes
Orange = Column headers
Blue = groupings (e.g. would expect four documents to be output based on this sample). I intentionally left out AC4 in the 'Account Code' column as this will be a weekly process and not all Account Codes will appear each week

In this scenario I would expect four word documents to be generated:

AC1 with 6 rows in the table of the document
AC2/AC3/AC5 all with three rows in their respective/independent documents

I have created a word template with the below fields (above is condensed for illustration purposes) and have highlighted the row and selected 'Repeating Section Content Control' in Developer mode.

Specific question in relation to the template - I'm assuming it is the entire row that should be marked as repeating? Not just the Account Code field?

Summary

After many failed attempts I'm wondering if anyone can help with this??

I have managed to build a flow that generates a document for each account code listed but only takes the first row of data (N.B. Some Account Codes may genuinely only have one row of data where some have multiple).

Any help at all would be greatly appreciated!

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

AlexEncodian 4,409 Moderator on at

Like (0)

Report

@cepaterson

Hi, can I ask is the excel data contained within a table content control or is it just free form excel? Do you have a preference for whether its excel or a list? And is it always the same few account codes or could it the number of account codes expand / could there be a large number of them?

Was this reply helpful? Yes No
cepaterson 4 on at

Like (0)

Report

Hi @AlexEncodian
1. It is in a table format (flows referencing Table1)
2. No preference as to whether it is excel or a list but thought excel in sharepoint might be better as the flow would be looking for the file name/path instead of having to delete and re-enter items on a list/append to the bottom of an existing list as it would become too large?
3. Re volumes max could be around 2,000 rows with a few hundred Account Codes (e.g. groups)

Was this reply helpful? Yes No
cepaterson 4 on at

Like (0)

Report

Hi @AlexEncodian thank you so much for the above! I'll give it a try!

Can I just check, in your second set of screenshots, where you have 'Initialize Variable' for Unique Accounts/Doc Data etc, are the paramaters for these steps:

Initialize Variable
Array
Value - selecting the variable you've listed (e.g. Unique Accounts)?

Through my test runs I have encountered errors stating that the flow time has run out/flow has stopped. With such a large data set, do you have any suggestions outwith Power Automate that would allow me to populate the word document from excel? Is it worth exploring Power Query also?

Thanks again!

Was this reply helpful? Yes No
AlexEncodian 4,409 Moderator on at

Like (0)

Report

@cepaterson

I left the value blank when initialising the variables. They get populated later (array type is correct)

I'm not sure which part of your flow is running into limits. Can you see in the run history?

Apply to each should be able to handle 5,000 iterations. You may have to manually increase limits for the list rows in excel action which used to have a default limit of 256 rows

Retrieve More Than The Default Number Of Rows Of Data From Excel Using Power Automate (c-sharpcorner.com)

In your test run, how many rows did you have / how many unique accounts? Is the flow working with smaller test data?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Can you please elaborate why you used two different initialize variable actions after the first apply to each section and what they are used for? What where the parameters for those? Thanks in advance!

Was this reply helpful? Yes No