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

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"