Using Dataflows for Iterative Excel File Processing in OneDrive and Upsert in Dataverse

[HamidBee]

Hi All,

I'm exploring the capabilities of Power Automate and Dataflows for a scenario where Excel files are periodically added to a OneDrive folder, each containing a table that needs to be upserted to a Dataverse table. Based on an enlightening conversation in a previous thread, I have a few questions about the feasibility and setup of Dataflows for this use case:

1. Can Dataflows be configured to continuously sync with a designated OneDrive folder? I'm looking for a way to ensure that if a row is edited, deleted, or added in any Excel file within this folder, it will automatically trigger the Dataflow.

2. Is there a frequency limit to how often Dataflows can run within a day? Are they primarily schedule-based, or can they be event-triggered, responding to changes in the OneDrive folder?

3. How does the upsert method via the Dataverse Web API differ from Dataflows? Is one more efficient or suitable for handling larger datasets (in the millions of rows)?

4. In the previous advice received, the process involved specifying the table name in the 'List rows present in a table' action. However, if the table names are not consistent or subject to change, how can we accommodate such variations? Is there a dynamic way to reference these tables without hardcoding the names?

The overarching goal is to create a robust and scalable solution that can manage data synchronization from Excel to Dataverse as efficiently as possible, considering datasets that will range from a few hundred to millions of rows.

I appreciate the community's input and look forward to your suggestions and insights.

[HamidBee]

Here is an image below showing a limit:

"The refresh frequency exceeds the allowed limit of 48 refreshes per day. Please select a value greater than or equal to 30."

[v-rongtiep-msft]

Hi @HamidBee ,

Please refer to.

• Dataflows can be configured to continuously sync with a designated OneDrive folder. You can use the “When a file is created or modified (properties only)” trigger in Power Automate to detect changes in the OneDrive folder and trigger the Dataflow accordingly.
• There is no frequency limit to how often Dataflows can run within a day. They can be scheduled to run at specific times or triggered by events such as changes in the OneDrive folder.
• The upsert method via the Dataverse Web API and Dataflows both enable you to create or update records in Dataverse. However, the upsert method is more efficient when loading data into Dataverse from an external system, especially in bulk data integration scenarios where you may not know if a record already exists in Dataverse. In such cases, you can’t know if you should use the Update or a Create message. You must retrieve the record first to determine if it exists before performing the appropriate operation. You can reduce this complexity and load data into Dataverse more efficiently by using the Upsert message.
• If the table names are not consistent or subject to change, you can use dynamic content in Power Automate to reference these tables without hardcoding the names. For example, you can use the “List tables” action to retrieve a list of tables in Dataverse and then use the “Apply to each” action to loop through the tables and perform the desired operation.

Use Upsert to Create or Update a record (Microsoft Dataverse) - Power Apps | Microsoft Learn

Sample: Insert or update record using Upsert (Microsoft Dataverse) - Power Apps | Microsoft Learn

dynamics crm - Dynamics365 web api update vs upsert - Stack Overflow

Dataflows Limitations, restrictions and supported connectors and features - Power BI | Microsoft Lea...

Refresh Power BI Queries Through Power Platform Dataflows: Unlimited Times with Any Frequency - RADA...

Solved: How do I reference a dynamic content in an express... - Power Platform Community (microsoft....

Use data operations in Power Automate (contains video) - Power Automate | Microsoft Learn

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.