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.

     

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.

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