Data Model - Daily Datasets

[Aimeeclaird]

Hi all, would like some help/wisdom here please.

I have a daily CRM download that I want to report on in PBI. I am struggling to think of the best way to set this up, without needing a great deal of editing each day.

I am specifically looking to compare each CRM entries progress, for example:

If ID123 was status 'New Lead' yesterday, and today the status is 'Appointment Booked' that is good.

If ID456 was status 'New Lead' yesterday and is still 'New Lead' why hasn't that Lead been progress?

I also want to monitor blanks as an encouragement to ensure all fields are being populated, so I want to compare:

Blog status field: no. of blanks yesterday = 10. 

Blog status field: no. of blanks today = 5

The key question, what would be the best way to achieve this? I'm thinking 2 tables, one for Yesterday and one for Today. I could join the data set based on the ID as this won't change for each record? Is this the most practical way to do it?

[edhans]

Hard to say without knowing more about your your data, but I'd start by dumping everything into a single folder and importing en masse.

As you import though, change the Combine operation in Power Query to include the file modified date with the data. The Combine operation will automatically hide that column. Simply unhide it once the Combine is done. That way everything in Power Query or Power BI knows when that data came from, so it is easy to figure out today's data vs yesterday's data vs a week ago if you keep that many in the folder.