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,

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

 

  • kattlees's avatar
    kattlees
    Post Patron
    8 years ago

    I guess to clarify, this won't stop the initial import of all records, but will only show 3 years of data for you to create visuals with. I didn't see how to stop the actual import.

  • sanjoyleo's avatar
    sanjoyleo
    Helper I
    8 years ago

    Thanks a lot kattlees !! It works .

     

    I can see after doing that  , I got below line in Qry .

    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInPreviousNYears([ResultTime], 2) or Date.IsInCurrentYear([ResultTime]))

     

    One question here -- It's not the case that all the Table's data has been fetched into my System (as I did import) and from there this filter has been applied ? Or this filter applied while fetching the data itself from SQL DB ?

     

    Thanks in Advance !

    • kattlees's avatar
      kattlees
      Post Patron
      8 years ago

      I believe all the data is imported in and then filtered by the date you chose. You can change the date and get more data if you want.

       

      If you don't want it to import at all, I would use parry2k solution as he is a power bi super user! He has helped me on numerous occassions. 

      • sanjoyleo's avatar
        sanjoyleo
        Helper I
        8 years ago

        To @parry2k 

         

        Can you please give your valuable feedback here ? I was thinking If my Transaction table has 10M of data , then pulling all records and then filter in Desktop is a good option or not as I am using Import as connection.

         

        Thanks

        Sanjoy