Forum Discussion
Limit import based on date
select filter on your date column and then choose parameter as show below in below circle, and select your parameter.
sanjoyleo 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.
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
---------------
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
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.
Thanks a lot kattlees !! It works .
I can see after doing that , I got below line in Qry .
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInPreviousNYears([ResultTime], 2) or Date.IsInCurrentYear([ResultTime]))
One question here -- It's not the case that all the Table's data has been fetched into my System (as I did import) and from there this filter has been applied ? Or this filter applied while fetching the data itself from SQL DB ?
Thanks in Advance !
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"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.
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"
