Filter Excel Column and populate repeating section of Word Template

[Mike44]

I'm not 100% sure I even know how to ask my question, but here it goes.  I have an Excel spreadsheet (i.e., ISP Provider Meeting Schedule) with column headings as such (i.e., Consumer Name, Provider, Meeting Date, Meeting Time, and Meeting Location).  I'm trying to create a flow that will filter or group the 'Provider' column, listing all meetings for each provider.  It should then populate a Word template which I've created.  The template has the following controls: Provider and Date (i.e., Date will be supplied by the date the flow runs) and then a repeating control with the following: Consumer Name, Meeting Date, Meeting Time, and Meeting Location.  The repeating section should list all of their meetings, if more than one.

I'm not really sure where to begin, but below is my start.  Note: I don't want to have to input the provider's name, unless there is no other way.  I'd prefer that it filter/group it for me.  Also, with the 'apply to each 2' section, it's creating a separate Word doc for each meeting.  For example, if a provider has more than one meeting, it creates multiple documents for each.  I don't want this to happen.  I want one document, listing all the meetings.

[v-xiaochen-msft]

Hi @Mike44 ,

I made a sample for you:

Create a table in excel.

Create a flow, and I will explain it step by step.

Create a empty array.

Get all rows in the table.

Get the column 'Provider' and group it.

union(body('Select'),body('Select'))

Filter for rows that match the current 'provider'.

Appends the contents of each row to the array.

{

    "Costumer Name": @{items('Apply_to_each_2')?['Consumer Name']},

    "Meeting Date": @{items('Apply_to_each_2')?['Meeting Date']},

    "Meeting Time": @{items('Apply_to_each_2')?['Meeting Time']},

    "Meeting Location": @{items('Apply_to_each_2')?['Meeting Location']}

  }

Create Word content, repeating section using array.

Create file.

Save and run flow.

Each provider creates a file.

The contents of document 'A' are as follows.

Best Regards,

Wearsky

[Mike44]

I appreciate the help.  I'm running into problems when I reach the 'Apply_to_each_2'.  I don't have a choice in dynamic content for {v} Body.

Also, I forgot to include 2 other columns in my table, Meeting Type and Service Coordinator, which need to be included in the 'Append to array variable' piece.  I tried to type them in with what you provided as such: 

"Meeting Type": @{items('Apply_to_each_2')?['Meeting Type']}, but was getting errors.  I typed them into Notepad++ and then copied, seems to be alright, but wanted to check before moving on:

If that part looks good, just need help with the 'Apply to each 2' before I can proceed.

Thanks.

[v-xiaochen-msft]

Hi @Mike44 ,

The body of the 'filter array' used in 'Apply_to_each_2'. You can also use expression to add formulas：

body(‘Filter_array’)

In the operation 'Append to array variable', I suggest you use expression to include the following function.

If there is a problem with other columns, you can also use this method to add functions.

items('Apply_to_each_2')?['Meeting Type']

items('Apply_to_each_2')?['Service Coordinator']

Best Regards,

Wearsky

[Mike44]

First, thank-you for your patience.  I'm still running into some problems.  When I test the flow, it produces the meeting schedule correctly for the first provider listed in Excel.  From there, for each provider after, it adds the previous providers meetings to each schedule.  I not sure if my layout is correct.  It's kind of difficult to tell in your screen shots whether some items are a 'next action' or 'next step'.  Also, I need to format the meeting date and meeting time.  In the 'List rows present in table' I chose ISO 8601 as the Date Time Format.  My results for both date and time are being displayed as: 1/2/1965 12:00:00 AM.  Where I need date to be displayed 1/2/65 and time 12:00 AM.  Below is a screen shot of the 'Apply to each' steps.

Here just a screen shot of the apply 2 each not expanded:

[Mike44]

In viewing the raw outputs, everything appears to be good up to populating the Word document.  It appears that this is where it's adding the meetings from the previous provider in the loop.  It doesn't appear, if it's supposed too, to be pulling the variable from the 'Append to array variable'.  When I hover over the dynamic content of the 'repeat section' ({x}Repeat) of the 'Populate Word', it says: variables('Repeat').  Not having much experience here, I'm not sure what it should say.  This is my only choice in the dynamic content to select a variable.  Other choices are from 'Apply to each', 'Compose', 'Filter Array', 'Apply to each 2', 'Select' and 'List rows present in table'.

[Mike44 · Accepted solution]

After hours of trial and error, I finally came up with a solution making some modifications to v-xiaochen-msft solution.  It appears that I did not need the 'Initialize Variable' or 'Append to Variable Array' in the flow, at least in my situation.  After removing them and making some other adjustment, I have it working perfectly.  Below are my results.

[eshimomura]

Hi Mike. Can you explain how you entered your column values to fill the word template? My flow is running and grabbed the correct number of entries for each "Provider" and created the correct number of rows in the template, but the values filled are blank. 

If you didn't use the initialize/append to variable, where did you enter these values?    

[Mike44]

Does your Word document have content controls and the table in the document have a repeating section content control?  If not, this video explains how to add them: https://www.youtube.com/watch?v=lBXSsEIlWok