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 want to import the last 5 or 10 years. Is this possible?

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.

     

32 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    create a date parameter in query editor and filter your table based on that parameter value.

    • kattlees's avatar
      kattlees
      Post Patron
      8 years ago

      I was able to find how to create a date parameter. how do I apply it to a query?

    • parry2k's avatar
      parry2k
      Super User
      8 years ago

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

       

      • kriman's avatar
        kriman
        Helper I
        8 years ago

        Hi,

         

        I used the advanced filters (greater than or equal to & less than or equal to) to only get data between certain dates. For example between Sept 1st & Oct.31st of 2017.

         

        However, the filters don't seem to work, the data extraction keeps going and going trying to extract the entire table. This is not rocket science, just filters, but they don't work.

         

        Any thoughts ???

         

         

         

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I have the same problem and get a runtime error when I pull data in Power BI from a datacube of our navision system. How can I limit the query to one year which would be then 'lighter' and would hopefully work? Can someone help me with the code, which I could then enter manually? 

     

    Here is standard query in power bi:

     

    let
    Source = AnalysisServices.Databases("AZR-xxx\xxx", [TypedMeasureColumns=true, Implementation="2.0"]),
    SA4NAV4_NORD = Source{[Name="SA4NAV4_NORD"]}[Data],
    #"Perfect Order Analysis NAV1" = SA4NAV4_NORD{[Id="Perfect Order Analysis NAV4"]}[Data],
    #"Perfect Order Analysis NAV2" = #"Perfect Order Analysis NAV1"{[Id="Perfect Order Analysis NAV4"]}[Data],
    #"Added Items" = Cube.Transform(#"Perfect Order Analysis NAV2",