Add a date stamp to new data on scheduled refresh

[PBI_AMS]

I'm ultimately planning to build a snapshot table using Power Automate, but first I need to engineer a date stamp.  I'm querying a CRM database where users are toggling a field to indicate a change in status from null to "Tracked." There is no date stamp recoverable in the database, but I want to be able to report how many new "Tracked" records are added each week, month, quarter, year. I have UniqueID and, in Power Query have filtered the Tracked Y/N field to only show Y records in my table.  Let's say I start today and have 1000 records with the date of today (9/24/2024). When my query refreshes tomorrow, and there are now 1020 records, I want to have a column that preserves the 9/24 date stamp on the initial 1000 records and adds a 9/25 date stamp to the 20 new records.  Then I'll figure out the part about snapshotting the new data and appending it to the existing somewhere in sharepoint.  Is there a way to achieve this that I'm not considering? 

[christinepayton]

I would check with the admins of your CRM and see if it's already logging change history somewhere that you can query. Many of them do log history. Not sure if you're getting the data directly from the CRM or from a data warehouse, but there's options in SQL too for history logging/querying too. 

[PBI_AMS]

Yes, this was my initial effort and my hope! Unfortunately, they won't make the audit log data programmatically available and only retain it for 30 days anyway.  I'll do a bit of research on SQL options.  

[lbendlin · Accepted solution]

DAX has built in functions for that. Read about EXCEPT and INTERSECT.

[PBI_AMS]

Confirmed that this was exactly what I needed! 

[PBI_AMS]

This sounds pretty promising! Thank you for bringing to my attention.