How to Filter specific Excel Tables and loop through to make updates on them

(0) Share

Report

Posted on by Script_IT

Hi all

As a Power Automate newbie, I'm struggling to with the following...

I have an Excel Sheet on Share Point with many tables in there. Some of the table names starts with "Table61xxxx". They have all an identical architecture. I was able to do a Flow to modify one table as desired, works perfectly. Now I'm trying to filter for the rest of the tables starting with "Table61xxxx" and do some updates on them. So I want loop through the rest of them and do the updates in there, but I have no clue how to do that.

What I'm missing and can't manage is a loop through all tables in the Excel Sheet and a filter which only uses tables starting with "Table61xxxxx" as the next table to be processed and then processes the same updates as the first table. Currently, if there is a 0 in the "Mail sent" column, I want to send an email to the recipient.

For loop through tables, I tried to work with arrays and variables, unfortunately without success, because I somehow don't understand the principle. I mean mainly the syntax needed to apply the variables and how to access array values (table name) to be able to use them afterwards for the "Update a Row".

Hope this explains it a little better - thanks!

Picture 1: Section of the source Excel tables. On the left side of each table is the table name to filter by, but only as an example, this name is of course not in the Excel sheet, it's the Excel internal Table Name.

Picture 2 & 3: my current workflow, which works, but with only one table (Table61), because I have stored this fixed.

So, what I need, is a procedure between "Get Tables" an "List Rows present in a Table" in Picture 2, to replace the "Table" as variable. All the rest seems to work already.

Can anyone help with an example for that?

Thx!

Chris

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

eliotcole 4,363 Moderator on at

Like (0)

Report

What have you tried so far, Chris?

I can't emphasise enough how important it is to actually try *something* rather than ask folks here to do it for you (sorry if that sounds harsh, it's not meant that way), because I think you'll find that you can probably figure it out purely by playing with the various actions in the Excel for business (or normal) connector's set.

Put a screenshot in your question of either one or a few attempts here, and perhaps give some example data for us to mull over, as it'll all really help with context if someone does try to assist you here.

Was this reply helpful? Yes No
Script_IT 6 on at

Like (1)

Report

Hello Eliot

Thanks for your feedback, that's totally fine ;-). Currently I have already invested many hours to come to a solution, but somehow I don't seem to get it yet.

Picture 1: Section of the source Excel tables. On the left side of each table is the table name to filter by, but only as an example, this name is of course not in the Excel sheet, it's the Excel internal Table Name.

Picture 2 & 3: my current workflow, which works, but with only one table (Table61), because I have stored this fixed.

What I'm missing and can't manage is a loop through all tables in the Excel Sheet and a filter which only uses tables starting with "Table61xxxxx" as the next table to be processed and then processes the same updates as the first table. Currently, if there is a 0 in the "Mail sent" column, I want to send an email to the recipient.

For loop through tables, I tried to work with arrays and variables, unfortunately without success, because I somehow don't understand the principle. I mean mainly the syntax needed to apply the variables and how to access array values (table name) to be able to use them afterwards for the "Update a Row".
Hope this explains it a little better - thanks!

Picture 1:

Picture 2 & 3:

Was this reply helpful? Yes No
Script_IT 6 on at

Like (0)

Report

So, what I need, is a procedure between "Get Tables" an "List Rows present in a Table" in Picture 2, to replace the "Table" as variable. All the rest seems to work already.

Thx!

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report

This is excellent, thanks for bringing it to the table. Also, if you can, copy all of that into your original question ... as it'll help folks get the full picture without wading through down here. 👍

I do think you will have someone get you a full fix, here. However, if there's no way you can't ditch the excel sheet altogether and move the data to a SharePoint/Microsoft List, or DataVerse table (which you really should with this data) then you could get a lot of benefit from having the excel sheet in its own specific folder on SharePoint/OneDrive (wherever it's currently kept) and ensure it's the only file in there.

There's then the *chance* that you could cut down flow runs by making the trigger fire on the SharePoint/OneDrive trigger for When a file is created or modified. This will mean that everytime the file is saved, it will run the flow. This isn't a perfect solution, because of O365s aggressive saving tactics (on every little thing) ... but if the sheet isn't updated that often, and your current recurrence IS often, it could really help.

I'm currently a little busy, but I'll keep this tab open on my browser if I find the time over the next couple of days.

Was this reply helpful? Yes No