Forum Discussion
How do I purge old data ?
Hi All,
I have a data for past 3months (getting from AWS server query datediff(day,test_date,current_date) <=90 ). but now i don't want to process 90 days daily rather i want to do only past one day and remove the data more than 90 days. Any ideas/suggestions will be appreciated.
Thanks in Advance !!
3 Replies
Hi Anonymous ,
You could calculate date difference in the query editor at first.
Table.AddColumn(#"Added Index", "Custom", each Duration.Days(DateTime.Date(DateTime.LocalNow())-[Your date column]))You can then retain data for less than 90 days.
Hi v-eachen-msft ,
Thanks for your reply. i feel my question is not clear. Let me explain my question correctly, i have below table which is having 100 million data and my dataset is not refreshing. so, i am trying to minimize the data for last 90 days by using parameters, so i can restrict the data during inital import only rather than importing and then filtering it. Currently i have 2 challenges:
1. When i try to pass the parameter to source query in advanced editor like below, i am getting error mesage.
2. After the intial import of 90 days, i wan't to refresh only the last day data instead of refreshing daily 90 days data
Source = Odbc.Query("dsn=Redshift", "SELECT invoice_no, invc_nm, invc_date FROM Invoice where date(invc_date) >= RangeStart and date(Invc_date) <=RangeEnd ;")
Notes:
Invc_date is time stamp
RangeStart is start parameter
RangeEnd is End parameter
Thanks in advance!
You could look at this video - https://channel9.msdn.com/Blogs/MVP-Azure/Pass-parameter-to-SQL-Queries-statement-using-Power-BI - it shows the syntax of passing parameters.
You could use incremental refresh if you have Premium - https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refreshYou can also utilise query folding by setting the parameters in Power Query (I haven't tried this out though) - see - https://docs.microsoft.com/en-us/power-query/power-query-folding
