Power Platform Community Forum Thread Details

Syncing Excel Census with Dataverse and SharePoint – Need Advice on Handling Transformations

Hi Power Platform Community! 👋

We are working on a data sync project at our company, and we’d love some advice on best practices.

Our Setup:

Excel Census (master file): Our HR system exports a full census of employee data into Excel daily. This is our only option—there’s no API or other integration available.
Dataverse: We use Power Automate/Dataflows to sync Excel with Dataverse, and Dataverse should act as a mirror of the Excel data.
SharePoint Child Lists: From Dataverse, we need to push data to multiple SharePoint lists (e.g., UFCW, Cafeteria, Communications), each containing a subset of the employee data in a different structure.

Our Challenges:

Transforming Data Before It Hits SharePoint:
We often need to transform or filter data before it gets sent to SharePoint. For example:
- Omitting leading zeroes from IDs.
- Filtering Pay Codes (e.g., only send records with Pay Code = "FAA").
- Adjusting data formats or excluding sensitive fields before syncing.
Doing this kind of data transformation mid-flow feels complicated, and we are struggling to figure out the best way to implement it efficiently.
How Should We Structure These Syncs?
We’re not sure if it’s better to:
- Set up child Dataverse tables and flows to keep subsets of data in sync with the master, and sync those to SharePoint.
- Or, is there a better architecture we can use to manage these transformations between Dataverse and SharePoint?

What We Need Help With:

How can we handle these kinds of data transformations efficiently during the sync process?
Should we use multiple child flows/tables in Dataverse to break things up, or is there a better way to structure this with our current setup?
Any tips or best practices for managing transformations between Dataverse and SharePoint in Power Automate?

We’re aiming to build a reliable solution that accurately reflects all changes made to the Excel Census across Dataverse and SharePoint without reloading entire lists every day.

Thanks so much for your help! Any insights or recommendations are greatly appreciated.

You may be able to significantly modify this template set up to track any changes to the Excel data each day & then push only those changes to Dataverse & SharePoint

https://community.powerplatform.com/galleries/gallery-posts/?postid=e1e47e8f-9bfd-4d9c-9b43-68ced3a6cbfb

Essentially instead of triggering the flow every time an Excel file is modified on SharePoint you would just trigger the flow with the arrival of the new HR system file, then the flow would proceed to check the new version of the Excel table data against the JSON file copy of the previous version of the Excel data to identify additions, modifications, & deletions. Then you could set your needed Dataverse & SharePoint actions in the appropriate Apply to each loops for Creations, Modifications, & Deletions.

Quick Links