Excel to Dataverse Records History Log

[AhmedSalih]

USE CASE

A company is migrating from Excel to Dataverse. While they are building the Dataflows to move the Excel data sheets to the Dataverse Tables, they want to keep track of all Excel sheets data changes created by the users during the migration. 

Watch the Video

DATA

1️⃣ There is an Excel Sheet that has a subset of the HR data, and it tracks current employees' Full Names, Employee IDs, Departments Names, Supervisors' Names, PayCode, Office Numbers and the PC Asset Tag Number.

2️⃣ I created a Dataverse table with the same columns, and I added one extra custom column to hold the value of the Log Change that we will create in the Power Query of the DataFlow later. This Column is a Text Type with a maximum length of 100 characters.  

3️⃣ Then I created an  Alternate Key and assigned it to this Log Key Column. This Column will only have Unique values. 

DataFLows

1️⃣ Then, I created a DataFlows and used the HR Excel file as my data source. I already had the file in a shared OneDrive folder. 

2️⃣ I selected the Excel Data Source and Selected the HR Excel File from the OneDrive Folder.  

3️⃣ Now, I need to determine what data columns can be changed by the users using the Excel sheet. In our example, the HR users can change the Department Names, Supervisor Names, Pay codes, Office Numbers, and PC Tag Numbers. Also, I want to determine one or two columns that won't change, and in this example, I will select the Employee ID. Then, in the  Power Query Editor, I will duplicate those columns, merge those copies, and rename the merged new column as the Log Key. 

4️⃣ In the next screen, we will finalize the columns mapping between the source and destination and select our Dataverse  Alternate Key. 

RESULT

After we published the DataFlow and refreshed the Data, we have the Data loaded into the Dataverse Table as below. Now if a HR user change any of current Excel data or added new data records, the old and new data will be saved in this Dataverse table. 

DATAFLOW SCHEDULE SETTINGS

For our use case, I am going to setup the Dataflows to run every hour. Different use cases may requires different frequencies. 

POWER AUTOMATE AND DATAFLOWS

Let us say that we have our source data in Dataverse, SQL, or SharePoint, we can use Power Automate Triggers to run the DataFlows whenever there is a change in the data source tables. This will help us make sure that the DataFlows only run to retrieve the changes when the changes occur in the source data tables.