cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Script_IT
New Member

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

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.

 

Script_IT_0-1658328151698.png

 

 

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

 

Script_IT_1-1658328151527.png

 

 

Script_IT_2-1658328151524.png

 

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

4 REPLIES 4
eliotcole
Super User
Super User

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.

Script_IT
New Member

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:

Script_IT_1-1658254500975.png

 

Picture 2 & 3:

Script_IT_0-1658254111624.png

 

Script_IT_2-1658254559656.png

 

 

Script_IT
New Member

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!

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.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (6,206)