Advice about excluding old data

[ArchStanton]

Hi,

My data model is connect to a Live Dynamics CRM system, my report refreshes daily (overnight) and everything is working well.
However, I have a lot of old data from 2018 & 2019 that is not used and I wish to exclude it from my main fact table.

What is the best way to do this?

My guess is to apply a filter on the Created On date in Power Query that excludes all transactions before 1st Apr 2020. The data that was migrated onto our system is of very poor quality therefore any reporting is based on 2020 onwards where data quality is much better.

I just want to check if that is the best way?

I have a lot of existing data transformation steps so should I'm thinking of adding the filter step early on?
The data model is very complicated and the fact table current has ~60,000 rows and 48 columns, removing these 2 yrs will reduce ~4000 rows of rubbish.

[cengizhanarslan · Accepted solution]

If you are sure the business will never report on that migrated 2018–2019 data, then filtering it out in Power Query is the right approach. First, add the filter as early as possible in the query steps, ideally right after the source and type-setting steps. That gives Power BI the best chance to fold the filter back to Dynamics/Dataverse, so fewer rows are pulled before the later transformations run.

[ArchStanton]

Noted, thanks!