Forum Discussion

miltenburger's avatar
miltenburger
Helper V
8 years ago
Solved

Automatically delete data from import

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

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    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. :smileyhappy:

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

     

    Regards

10 Replies

  • Abduvali's avatar
    Abduvali
    Skilled Sharer
    8 years ago

    Hi miltenburger,

     

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

     

     

    Regards

    Abduvali

    • miltenburger's avatar
      miltenburger
      Helper V
      8 years ago

      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's avatar
        Abduvali
        Skilled Sharer
        8 years ago

        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

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

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