Skip to main content

Notifications

Community site session details

Community site session details

Session Id : URmvYVDeifAz7Qu1p5Toej
Power Automate - Building Flows
Suggested answer

MS Forms, Tables, Excel !

Like (1) ShareShare
ReportReport
Posted on 14 Mar 2025 15:30:57 by 6
Hi friends.
Here is my flow plan for the following scenario
 
I have a master excel worksheet with around 50 consecutive differently named Tables (all with the same format and headers, all 4 columns) some with up to 20 rows.
I was trying to use MS Forms (with same table names) to give a selection page to then choose which of the 50 tables you wished to work with.
This then creates an array. Power automate ideally would then get each of these tables from the master excel sheet and then create a brand new Excel document  and paste these chosen tables into this document ( I wasn't sure if create table connector was needed).
 
I have done all sorts of combinations, variables, composes, apply to eaches and just cant get it to work with errors ranging from Cant overlap tables to not correct array format.
 
Does anyone have any templates they've used for similar ?  Here is my (failed) flow attempt 
 
  • GW-28091308-0 Profile Picture
    6 on 14 Mar 2025 at 20:19:09
    MS Forms, Tables, Excel !
    Ok, so I have had a look.  Here are my queries.
     
    1. For Copy File - when copying a template - am I creating a standalone blank document with nothing in it - then using that as to copy from - (or am I using the master excel with all the data and tables) ?
    2. Making a environment / look up might be slightly beyond me.  I have each individual Table found in the master Excel document also as individual Sharepoint lists (ie a list for each table)- might that be an option to use in some way ?
    3. I need to make it work for multiple table selections  (out of the 50+ tables found).  This is where I have run in to issue with "You can not create tables with overlap" I think,   especially when I try and use an additional "apply to each" to load all the selected tables from the MS Form output and before listing rows.
    My Compose/Initialise Variable steps were suppose to draw out the Table selections then I tried to use "Current Item" as the dynamic content for each
     
    Does any of that make any sense ?
     
     
  • GW-28091308-0 Profile Picture
    6 on 14 Mar 2025 at 18:49:57
    MS Forms, Tables, Excel !
    oh wow!  thanks for great and prompt response. You've obviously got exactly what I need.  Let me give it a go and see if it fits scenario and will reply soon!   thank you
  • Suggested answer
    Michael E. Gernaey Profile Picture
    36,015 Super User 2025 Season 1 on 14 Mar 2025 at 17:03:47
    MS Forms, Tables, Excel !
     
     
    So the mistake I see initially is that is "appears" that you are grabbing the master file, then using that to create the secondary file. The issue then would be that you are copying tables that already exist into the new file.
     
    My suggestion here, is that you actually have a blank Excel file, that you use to "make a blank copy of".
    Then you can create a sheet
    then you can create a Table
     
    then you can copy your data.
     
    So it would be something like
    1. Triggered Automated Flow
     
    2. Get the response details
     
    3. Copy the File
    Note: I don't know how you want to name the file, but I suggest again that you have a Template (blank) excel file that you use to copy versus trying to copy the master file and then interacting with it.
     
    Let me know if this would work for you or if you need other things to happen also
     
    4. Create the Table
    The definition of the table, IMHO should be mapped in a SharePoint Configuration table or Environment Variable. This would allow you to do a LookUp on the table name that you were given from the Form, then receive back a comma separated list of Columns to create
     
    5. List the rows in your master file + Table that was passed in
     
    6. Apply to each
    -Inside do your Add a row into a table action
     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Thomas Rice – Community Spotlight

We are honored to recognize Thomas Rice as our March 2025 Community…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,508 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,431 Most Valuable Professional

Leaderboard
Loading started
Loading complete