Forum Discussion

kattlees's avatar
kattlees
Icon for Post Patron rankPost 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
Icon for Helper I rankHelper 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
    Icon for Post Patron rankPost 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
      Icon for Super User rankSuper 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
        Icon for Helper I rankHelper 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

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