Forum Discussion
Post PatronLimit import based on date
select filter on your date column and then choose parameter as show below in below circle, and select your parameter.
Post PatronI 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.
Super Usersanjoyleo 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.
sanjoyleoHelper I
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---------------
- kattlees
This is how I would do it.
Click on your EDIT QUERIES tab
Select the filed you want to limit to 3 years
Click on the drop down arrow to the right of that field
Choose Date Filters
Choose In the Previous
When Filter Rows comes up, have it read is in the previous 2 years then OR
is in year This Year
See screenshot
- kattlees
I guess to clarify, this won't stop the initial import of all records, but will only show 3 years of data for you to create visuals with. I didn't see how to stop the actual import.
- parry2k
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
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"