How to create a flow that will iterate through all of the Excel files in a folder and iterate through all of the tables?

[HamidBee]

Hi,

I have a folder that we will populate with Excel files. In those Excel files there will be one table. This folder will be populated monthly. When a file is added to the OneDrive folder I will like the flow to be triggered. The idea is that all the rows in the tables of each file will be retrieved and then a Dataverse table will be updated. So either a row will be added or updated. Here is the flow I have so far:

Thanks in advance.

[Expiscornovus]

Hi @HamidBee,

How much data/rows per Excel file would we be talking about in this scenario? If this is a lot of data I am not sure if Power Automate cloud flows are the best option to meet this requirement.

You might want to consider using the out of the box import feature (which supports upserting), data flows or even the upsert method from the Dataverse Web API.

If you still want to use a cloud flow for this you could start by using the value field in the apply to each. After that you can retrieve the rows per table. For each of that row you will need to check if there is a matching item, via a list rows action. In the filter rows field you can provide an OData query which tries to find a match based on one of your key columns in Dataverse table.

[HamidBee]

Hi @Expiscornovus. Thank you for your informative reply.

---

How much data/rows per Excel file would we be talking about in this scenario? If this is a lot of data I am not sure if Power Automate cloud flows are the best option to meet this requirement.

Each table will have a couple of hundred of rows. However, there will be other projects that I will work on with millions of rows of data.

---

You might want to consider using the out of the box import feature (which supports upserting), data flows or even the upsert method from the Dataverse Web API.

Thanks for mentioning the dataflow. Regarding the dataflow method can I set it up so that it works with what I'm doing. So can I have it constantly syncing with my designated OneDrive folder where all of my Excel files are stored so that if a row is edited, deleted added it will automatically trigger the dataflow. I've never used dataflows before so I'm not too familiar with their capabilities. Is there a limit to how often it can run in a day? does it work on schedule or can it be triggered?.

You mentioned the upsert method from the dataverse web api. I've heard this terminology used when talking about delta lakes however I'm not sure how it works in dataverse. How does this differ from dataflows?.

Regarding the image you kindly shared, where you have "List rows present in a table" and underneath it you have "Table" what should I do if I don't want to set a table name. I'm assuming this prompt is asking me to enter the name of the table so it knows what to look for but what if this changes or the naming system isn't consistent. Is there a way to allow room for this?.

Thanks in advance. 

[Expiscornovus]

Hi @HamidBee,

It can be manually started or automatically run on a schedule. I believe the refresh schedule of dataflows can even be minutes. Have a look at this doc:

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/create-and-use-dataflows#set-the-re...

Regarding the table question. You could use a Get tables action and use the id of the first retrieved row (assuming the Excel only has one table) in the list rows present in table action.

You can use an expression for this:

first(outputs('Get_tables')?['body/value'])['id']