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

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

parry2k's avatar
parry2k
Super User
8 years ago
let
Source = Sql.Databases("179.90.2.99"),
#"Year" = Date.Year(DateTime.LocalNow())-3,
WebFT_SanjoyB = Source{[Name="WebFT_SanjoyB"]}[Data],
dbo_TestResults = WebFT_SanjoyB{[Schema="dbo",Item="TestResults"]}[Data]
#"Filtered Rows" = Table.SelectRows(#"dbo_TestResults", each Date.Year([Scheduled Date]) >= #"Year")
in
    #"Filtered Rows"

Above red lines are the changes in your code and replace blue [Scheduled Date] with the date column name in your model.

 

This will do it.

 

Thanks,

P

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi parry 2k,

     

    Hoping you can help me with something similar. My table has 10 years worth of data as well, but I would like to filter on a date before all 10 yrs load. Ideally I would like to load current month or current year, either one works. Here is the info:

     

     let
        Source = Odbc.DataSource("dsn=Chempax Live", [HierarchicalNavigation=true]),
        CHEMPAX_Database = Source{[Name="CHEMPAX",Kind="Database"]}[Data],
        SQLVIEW_Schema = CHEMPAX_Database{[Name="SQLVIEW",Kind="Schema"]}[Data],
        #"BATCH-REC-HDR_View" = SQLVIEW_Schema{[Name="BATCH-REC-HDR",Kind="View"]}[Data],
        #"Filtered Rows" = Table.SelectRows(#"BATCH-REC-HDR_View", each Date.IsInCurrentMonth([#"Receipt-Date"]))
    in
        #"Filtered Rows"

    • kattlees's avatar
      kattlees
      Post Patron
      6 years ago

      parry  

      In query editor, I opened up the source of the table and clicked on advanced options and wrote my own sql statement to limit by date.

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous let me know if you still need assistance.

  • Laura_Saoirse's avatar
    Laura_Saoirse
    Frequent Visitor
    4 years ago

    Hi there, thanks for this support. When I try to apply this code I get a 'Commen Token Expected' error on the #'Filtered Rows' (4th row) - Why might this be? I am pulling data from Dynamics 365, within the donations table, I want to filter by the 'year' column to only include this year YTD + prior two years 

     

    let

        Source = OData.Feed("https://xx.api.crm4.dynamics.com/api/data/v8.2/"),

        #"FilteredYear" = Date.Year(DateTime.LocalNow())-3,

        mh_donations_table = Source{[Name="mh_donations",Signature="table"]}[Data]

        #"FilteredRows" = Table.SelectRows(#"mh_donations_table", each Date.Year([Year]) >= #"FilteredYear")

    in

        #"FilteredRows"