How can I refresh data daily but maintain a certain amount of data each time?

[wpf_]

I have pbix file that I published to the service and I refresh data daily through the on prem gateway.  The dataset starts from 2009 and every day I refresh the dataset grows.  I'd like to just maintain 10 years of data at a maximum.  Is there a way to refresh to get up to the latest data, but not keep anything older then 10 years ,meaning some of the earlier data I would want to remove from the dataset.  And this needs to be done everyday I refresh.  

I am getting data from on prem data source (oracle).  I understand there is direct query, but I have built out tons of measures, and calculated columns, and even added an additional table in power bi, so not sure if direct query would work.  Hope someone can help. Thanks. 

[nickyvv]

Hi @wpf_,
Maybe I'm thinking too easy? But can't you filter it in Power Query, to only show the last 10 years of data (based on a transactional date column from the data source)? You can use a relative date filter for that.
You could even create a parameter to change the value from Desktop to the Service.

[wpf_]

@nickyvv ,

I dont want that big a dataset in my pbix or workspace in service.  Currently it is fine as the file is only about 45 mb, but if i keep refreshing daily this will get bigger.  So i am trying to cap my data to the last 10 years at any time.  Is there a work around for this?  Thanks

[Nimai123]

Hello @wpf_ 

I think the best and the easy workaround will be to filter out the data from your Date column and not allowing to load more than the past 10 years of Data from now.

You can also try to use the incremental load feature as it is now free which also can help you to achieve your goal as it uses query folding.

Links for incremental refresh

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

https://www.youtube.com/watch?v=9WLZ9jfoXwA

https://www.youtube.com/watch?v=BQahH53ayKU

IF YOU LIKE MY ANSWER PLEASE GIVE KUDOS AND IF MY ABOVE POST HELPS YOU TO SOLVE YOUR PROBLEM ACCEPT IT AS A SOLUTION. 

[wpf_]

@Nimai123 Thanks for the tip. 

If I filter out the data from my date column, wouldn't the underlying data be still in my pbix file?  For example if I am already importing 10 years of data, filtering out only 5 years, would not remove the other 5 years of data from my pbix file would it?  I want to just keep for example just 5 years of data in my file at all times so it will not grow.  

I checked out the incremental refreshes that you provided me thank you that was very helpful.  There is a feature there that could possibly do what I want, but I am not 100% sure if that is what it will do.  Hope you can clarify that if you have experience with that. Thanks.

[Nimai123 · Accepted solution]

Hello @wpf_ 

Let's assume you are using query folding, and you try to filter out the columns saying that you only want 5 yrs of Data out of 10 yrs, then the pbix will script the source for 5 yrs of Data and the whole 10 yrs of Data will not be loaded in the File. This will work only when that step is using QF

You can also try to script the M query in the Advanced Editor 

Query folding

https://docs.microsoft.com/en-us/power-bi/guidance/power-query-folding

https://docs.microsoft.com/en-us/power-query/power-query-folding

https://blog.pragmaticworks.com/power-bi-checking-query-folding-with-view-native-query

Regards,

IF YOU LIKE MY ANSWER PLEASE GIVE KUDOS AND IF MY ABOVE POST HELPS YOU TO SOLVE YOUR PROBLEM ACCEPT IT AS A SOLUTION. 

[wpf_]

@Nimai123 Thanks the progmaticworks link was very helpful.  So the querying is done with the data source level with query folding.  It makes sense now.

A couple points I need some clarification on:

1-So if I filter the dates to only always use the lastest 5 years in power query, is that considered using query folding?  My data source is oracle.  

2-incremental refresh can use query folding to achieve incremental refresh of data AND only get the latest 5 years of data?  

Thanks

[Nimai123]

Hello @wpf_ 

1.  No it is not considered as a query folding, QF means power bi is doing the transformations on the source side rather on Power BI.

2.  Yes.

Regards

[wpf_]

@Nimai123 Thanks for the help!