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.

     

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    parry2k 

    Yes, I do. If you have some time to help I would appreciate it!

     

    I tried to re-create your solution for someone else on this string to pull in 1 yrs worth of data instead of the full 10 years and then filtering. Do I have my #Year in the correct spot? This is an ODBC connection to an OpenEdge software called Chempax.

     

    let
        Source = Odbc.DataSource("dsn=Chempax Live", [HierarchicalNavigation=true]),
        #"Year" = Date.Year(DateTime.LocalNow())-1,
        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.Year([#"Receipt-Date"]) >= #"Year"
        )
    in
        #"Filtered Rows"