Skip to main content

Notifications

Power Automate - AI Builder
Suggested answer

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

(1) ShareShare
ReportReport
Posted on by 55

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.

  • KBpwr Profile Picture
    KBpwr 55 on at
    Syncing Excel Census with Dataverse and SharePoint – Need Advice on Handling Transformations
    That looks promising! Thanks for sharing.
  • Suggested answer
    takolota1 Profile Picture
    takolota1 4,790 on at
    Syncing Excel Census with Dataverse and SharePoint – Need Advice on Handling Transformations
    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
     
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard