Forum Discussion

kattlees's avatar
kattlees
Post Patron
8 years ago
Solved

Limit import based on date

I am new to Power BI and have decided to do import vs direct query.   Is there a way to limit the records imported based on date to save time/space? Our system has 20 years of data and if we only w...
Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    select filter on your date column and then choose parameter as show below in below circle, and select your parameter.

     

sanjoyleo's avatar
sanjoyleo
Helper I
8 years ago

Hi ,

 

How did you solve this ? I need the same...as I want to limit last 5 years of data while using Import option from SQL DB . Table has 10 years of data  . So it shuld always fetch this year i.e 2018 and 2017,2016, 2015,2014 .

 

And when it is  2019 then 2018,2017,2016, 2015..

 

Thanks in Advance

 

kattlees's avatar
kattlees
Post Patron
8 years ago

I went to the edit queries and just limited records by a specific date. Using the date functions, I think you can do last x number of years.

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    sanjoyleo I can help with you that. Not sure how familiar you are with Power Query "M" language, if you can send your query script, I will provide you the solution.

     

    Go to query editor, click advanced editore, and copy the script and paste it here.

    • sanjoyleo's avatar
      sanjoyleo
      Helper I
      8 years ago

      Hi,

      Thanks for your reply!! Please find below ..Table name is TestResults . It has a Column called "ResultTime" with Date-Time format ..sample values like :

      3/9/2017 3:39:53 PM

       

      So here What I want is to fetch only 3 years of data (2016, 2017,2018 )  and my table has 10 years of data.

      Please note from Jan 1st 2019 , data should be fetched automatically for 2017,2018,2019 ...

      So  basically last 2 years and current years which will be incremental.

       

      Please help  !

       

      -------------------

      let
      Source = Sql.Databases("179.90.2.99"),
      WebFT_SanjoyB = Source{[Name="WebFT_SanjoyB"]}[Data],
      dbo_TestResults = WebFT_SanjoyB{[Schema="dbo",Item="TestResults"]}[Data]
      in
      dbo_TestResults

      ---------------

      • kattlees's avatar
        kattlees
        Post Patron
        8 years ago

        This is how I would do it.

         

        Click on your EDIT QUERIES tab

        Select the filed you want to limit to 3 years

        Click on the drop down arrow to the right of that field

        Choose Date Filters

        Choose In the Previous

        When Filter Rows comes up, have it read is in the previous 2 years then OR

        is in year This Year

         

        See screenshot