Marcel Lehmann (mvp)

A common need is to provide data from multiple sources for applications like PowerApps, Power Virtual Agents, or Power BI solutions, ensuring effective data use. In this post, we explore how to seamlessly synchronize data from various systems using Virtual Tables and Dataflow to SharePoint. Notably, these files can be stored in a folder, which is automatically synced from the system to a SharePoint/OneDrive folder, making them accessible in the cloud.

Solution Overview:

1. Virtual Tables and SharePoint
2. Dataflow and Power Query for Data Updates
3. Power Platform Applications

Step 1: Virtual Tables and SharePoint

Creating a Virtual Table connects SharePoint and Dataverse, resulting in a Dataverse table. The key advantage of a Virtual Table is the 1:1 synchronization of data between Dataverse and a SharePoint list. Changes made in either table are instantly reflected in the other.

Step 2: Dataflow and Power Query for Data Updates

Once the Virtual Table is set up, Dataflow is used to synchronize data from the files to SharePoint via Dataverse. Dataflow, which incorporates Power Query, can be updated up to 48 times daily, either automatically or through a Power Automate action. CSV files from various systems are gathered in a folder. Power Query (DataFlow) reads, combines, and processes these files before storing the data in the Virtual Table.

Step 3: Power Platform Applications

With the synchronized data, you can develop Power Apps applications, Power Virtual Agents, and Power BI solutions, among others, that access up-to-date information. Users can now engage with the data via a user-friendly interface or use the data in their solution to map further processes based on the current data.

Conclusion:

Integrating data from multiple systems into the Power Platform can be streamlined and efficient using Virtual Tables and Dataflow. This approach facilitates optimal data utilization and simplifies working with the information in the applications. While Power Automate could also be an option, it might require employing Paul Murana’s solution, which demonstrates how to store batches of up to 1,000 items in SharePoint (https://www.tachytelic.net/2021/06/power-automate-flow-batch-create-sharepoint-list-items/) and avoids applying ‘apply to each’ across all items, resulting in faster performance.

NOTE:
Instead of using Virtual Tables & SharePoint, you can directly utilize Dataflows to synchronize data from various sources to Dataverse for Teams or Dataverse. Data sources can include different file types (e.g., CSV, Excel, XML), SharePoint folders, SQL databases