Automatically delete data from import

[miltenburger]

Hi all,

Currently i'm finishing my intership assignment. But i face 1 issue during refreshing my data.

Currently I import all my data into Power Bi (I also tried DirectQuery but it is way to slow).

I import all my data from a SQL Database, where I have a query which says: take all the data from now till 12 months ago. Works perfect.

But when I refresh my data next week, the SQL statement still works fine, but the data from 12 months ago + 1 week is still in my imported data list. Is there a way to automatically delete the data from 12 months / + 1 week (So for instance the data from 30-10-2016 till 5-11-2016)

Kind regards

[jthomson]

Should be possible in Power Query to run Date.IsInPreviousYear on your date field and filter out those that aren't

[Abduvali]

Hi @miltenburger,

Are you trying to display information between these days only exp:  30-10-2016 till 5-11-2016???

Regards

Abduvali

[miltenburger]

Hi @Abduvali,

Thanks for fast reply.

No I am displaying all the data from last 12 months. So for today this means the data from 6-11-2016 till 6-11-2017.

But my imported data still shows data from the weeks before that, and i want that data deleted. 

[Abduvali]

OK no need to delete anything just set a restriction under the Visual/Page or Report filters by dropping your date field in there:

• You can use relative date filtering like display data For the last 30 days or past calendar week only
• Advance filtering - display items when the date Is on or after (set date)

Advance FilteringRelative FilteringFilter Options

Hope this helps.

Regards

Abduvali

[miltenburger]

I get what you mean, and i tried it, it works.

But the data is still in my imported data. And i want it deleted...

Got any ideas on this? (your solution works fine for now btw)

[Abduvali]

Then just update your SQL query because you can't delete data from Dataset selectively, as Dataset is just a box whatever you put in there will sit there so must be done on query level. In your  SQL query instead of saying DATE >= '01/01/2016' use DATE BETWEEN  '01/01/2016' and  '01/01/2017' that way, you will only have the data within your preferred date range. 

[miltenburger]

I don't think that you understand my issue that well, so i try to be more clear since it has nothing tod o with my SQL query.

My sql query looks like this now:

WHERE CreatedDateTime >= DATEADD(month,-12,DATEADD(day,DATEDIFF(day,0,GETDATE()),0)) 
AND CreatedDateTime  <=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

This sql statement gives me the data from last 12 months based on todays date. 

Now when i push the refresh button to import the new data, it refreshes all my data, import the new dataset with data from now till 12 months ago. But the data that is older then 12 months ago (which i imported last week) , is still in the dataset. This is what i want te see removed. 

[Abduvali]

Then just remove second line of code and just keep:

WHERE CreatedDateTime >= DATEADD(month,-12,DATEADD(day,DATEDIFF(day,0,GETDATE()),0)) 

This should resolve your issue!!!

Regards

Abduvali 

[v-ljerr-msft · Accepted solution]

Hi @Abduvali,

Instead of using SQL statement, you should also be able to simply use Date.IsInPreviousMonth function(M) in Advanced Editor to import data from now till 12 months ago. 

    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each Date.IsInPreviousNMonths([FullDateAlternateKey], 12))
in
    #"Filtered Rows"

Regards

[Abduvali]

@v-ljerr-msft,

Thank you =D Will try it out sometime!!!