Tracking changes to data when refreshing the data source

[JonathanR]

Hi All,

I have a dataset that is connected to a spreadsheet (via web link) and I plan to set up a weekly automatic refresh. The spreadsheet is where all the data is changed/new rows are created.

The refresh functionality should be fairly straightforward, but I am trying to think of a way to track the historical datasets so that I can compare what has changed week on week (i.e., highlight any data/inputs that have changed in the current refresh, compared to the dataset a week prior).

For context, it is CRM data, so I would like to track any new rows/leads that have been added to the dataset or any status changes that were made (i.e., the status of one lead went from 'potential' to 'likely', so the count of leads that were likely went from 8 to 9).

Hopefully it makes sense. Thanks in advance.

[pankajnamekar25 · Accepted solution]

Hello @JonathanR 

Power BI alone doesn’t retain past data after refresh. To compare week-on-week:

Create a Historical Table in your data model

This stores all past versions of your data with a SnapshotDate column.

Automate appending data weekly

Use Power Automate, Power BI Dataflow, or an intermediate tool (like Power Query in Excel/SharePoint) to append the current week's snapshot to a long-term historical table.

Add a column like SnapshotDate = DateTime.LocalNow() during each refresh.

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Akash_Varuna · Accepted solution]

@JonathanR To track changes in your dataset during weekly refreshes, save each refreshed dataset into a "snapshot" table to retain historical data. Use queries or calculated columns to compare current data with the previous snapshot, identifying new rows or changes in specific fields. Create visuals to highlight these differences, such as tables for new entries or charts for status changes. Automate the process with Power Query and scheduled refresh to maintain consistent tracking.

[v-sathmakuri]

Hi @JonathanR ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[v-sathmakuri]

Hi @JonathanR ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[Akash_Varuna · Accepted solution]

@JonathanR To track changes in your dataset during weekly refreshes, save each refreshed dataset into a "snapshot" table to retain historical data. Use queries or calculated columns to compare current data with the previous snapshot, identifying new rows or changes in specific fields. Create visuals to highlight these differences, such as tables for new entries or charts for status changes. Automate the process with Power Query and scheduled refresh to maintain consistent tracking.

[JonathanR]

Hi Akash,

Thankyou for your response. Would I need to use power automate to save each refresh into a table? Or can it all be done via power bi desktop/power query? If you could point me to any demo's or resources that would be amazing. Thanks.

[v-sathmakuri · Accepted solution]

Hi @JonathanR ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Akash_Varuna  and @pankajnamekar25  for the prompt response.

Create a pipeline and a warehouse in Microsoft Fabric.

Use the Copy Data activity having your spreadsheet as the source and the warehouse as the destination. Load the data into a temporary table within the warehouse.

Then, use a stored procedure to handle changes tracking, insert new records with the current timestamp using GETDATE().

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!

[pankajnamekar25 · Accepted solution]

Hello @JonathanR 

Power BI alone doesn’t retain past data after refresh. To compare week-on-week:

Create a Historical Table in your data model

This stores all past versions of your data with a SnapshotDate column.

Automate appending data weekly

Use Power Automate, Power BI Dataflow, or an intermediate tool (like Power Query in Excel/SharePoint) to append the current week's snapshot to a long-term historical table.

Add a column like SnapshotDate = DateTime.LocalNow() during each refresh.

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.