Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
DAX has built in functions for that. Read about EXCEPT and INTERSECT.
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.
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.
DAX has built in functions for that. Read about EXCEPT and INTERSECT.
Confirmed that this was exactly what I needed!
This sounds pretty promising! Thank you for bringing to my attention.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
37 | |
30 | |
18 | |
14 | |
8 |
User | Count |
---|---|
50 | |
38 | |
31 | |
15 | |
13 |