Updating Excel rows: table ID

[polo1604]

Hello,

I have a flow getting data from sharepoint list to update an excel file with several tables.

One of the columns of the sharepoint list matches with the table names of the excel worksheet.

To update data in on specific table I use this function:

I would like to update all tables in a row. I have the table names of of getting excel tables function, but if I put the result in the table field, power automate doesn't find the items to populate.

Is there a workaround to realize this?

Thanks for your help !

Paul

[jedunn]

It sounds like the table names you pass into your power automate steps aren't being recognized. It's hard for me to troubleshoot possible solutions. Do you happen to have a picture of the error or what the outputs look like on run?

One thought is to consider using an Office Script to route the data in the workbook with the Excel Run Script action.

[polo1604]

Hi @jedunn 

thanks for your quick reply. In my screenshot of the first post, you see that the excel fields to be populated are shown as soon as I choose on of the tables in the dropdown list.

But what I'm trying is to give a variable in the table field, therefore the excel columns are not shown.

I'm looking for dynamic allocation to all tables of the excel spreadsheet. Unfortunately, an Office script is not a sulution as it is not in my O365 package.

thanks again

Paul

[rubin_boer]

hi @polo1604 here is something you can use (i hope)

Scenario:

two tables in one Excel spreadhseet

Table1 = TableOne

Table2 = TableTwo

Both tables have the same header name and same key column (ID)

Header names ID, SomeName, Value, TwiceTheValue

something like this

Now the flow to update first line (key = 1)

1. Get tables
2. Update row (this is where you may need to have a variable if your headers of your tables differ)
   1. {"SomeName":"From Power Automate","Value":"100","TwiceTheValue":"200"} - your values to be added in this format

       

Result

1. 

Hope this helps,

R

[polo1604]

Hi @rubin_boer 

thanks a lot for your quick reply, this helps, I can populate now my excel tables out of my sharepoint list.

But all tables are filled with the same data, where can I filter them in order to have only the data for table1 in table1 and those for table2 in table2, etc.?

the structure of the sharepoint list is that there is a column giving the tableID in each row.

regards

Paul

[jedunn]

Hello - It looks like @rubin_boer's post proves that you can build a flow that recognizes a dynamic table name. That's fantastic! I think you just need to build some logic that appropriately routes the data based on that table name. I'm still learning how to create flows effectively, so these are just a couple untested ideas:

• What if you had some if conditions in the flow that read the table name and routed things appropriately? The pseudo logic might be something like this: for each value, if someColumnName's data is "foo", then update row in table1, else if someColumnName's data is "boo", then update row in table2... and so on.
• Another idea is to use an expression instead of just the dynamic tableName. In theory, I believe this expression could contain similar logic for matching a columnName to a table name. This is a bit beyond my skills and knowledge but I think it's possible.

Good luck. Sorry I can't offer more definitive guidance.

[jedunn]

To learn more about how and where to use expressions in flows, take a look at this training module - Introduction to expressions in Power Automate - Learn | Microsoft Docs.

[rubin_boer · Accepted solution]

hi @polo1604 

Are you winning with this one?

here is an example

Consider a SharePoint List "Excel Table Master"

Objective: move data to excel via flow

Steps

1. Get data from Excel (Get Items)
2. Update Excel for each entry by selecting the right table

Flow

Result

File attatched

Hope it helps,

R

[polo1604]

Hi @rubin_boer 

I won ! 😀

just incredible, so easy, logical!

Thanks a lot

best regards

Paul