How to automate your data to always store the last 2 years of data using Power Query
How cool would it be to not have to manually update your dataset to keep data for last 2 Years (last year and this year to the current date)?
In this blog post I will show you how you can easily filter dates in Power Query to show dates for last year and year to date by using the GUI and not having to hardcode anything.
I must admit the first time I saw this was when I watching my good friend Reid Havens presenting. Thanks Reid!
Below are the steps to get it done to show how I completed the configuration:
- Last Year
- Year to date
Making the changes
- I went into Power Query
-
I went to my table where I had my Date column.
- In my example it was the column called Date
-
I then made sure that the Data Type for the column was set to Date
-
I then click on the down arrow and select Date Filters (1) and then Custom Filter…(2) on the bottom
-
When the Filter Rows Window pops up, I then clicked on Advanced
-
Now, this is where the magic happens, I am first going to configure it for Last Year
- Under Operator where it says “equals” I click on the drop down and select “is in year”
-
Then under Value I then selected “Last Year”
- The first row I have configured for Last Year looks like the following below.
-
Next, I am going to configure it for Year to Date.
- On the next line I change the second condition from And to Or
- Under Operator where it says “equals” I click on the drop down and select “is in year”
-
Then under Value I then selected “Year To Date”
- After configuring both rows I now see the following in the Filter Rows window
- I then clicked Ok
-
Now when I look at my table, I can see that the starting date for my table is the start of last year which is 01/01/2019
-
Likewise for me to check that it only has got the dates up until todays date (2020-08-31)
- I did this by sorting the table descending
Summary
In this blog post I have shown how I configured my dataset to always keep my data current with last year’s data and this year’s data.
By using the built-in features of Power Query, I do not have to update anything manually and it will automatically update.
Thanks for reading, comments and suggestions are most welcome.
[…] Gilbert Quevauvilliers has an easy method of constraining data sizes in Power Query: […]
Nice to know, can you please help on getting the similar logic for last 2 year (i.e, from 01-Jan-2019 to tilldate (08-Jul-2021))
Hi Saravanan,
I would suggest you use the Date table and use that to limit the data for the last 2 years.
I am not sure it is possible using the Power Query
Hi,
I know it’s a late reply but might help someone else landing on this page.
I used the same concept as described in this blog but used these criterias instead:
“is in the previous” – “2” – “years”
or
“is in year” – “this year”
Resulting in the following M code:
= Table.SelectRows(#”Removed Other Columns”, each Date.IsInPreviousNYears([Posting Date], 2) or Date.IsInCurrentYear([Posting Date]))
Awesome thanks for sharing!
hello，I would like to know how to filter 3 years by power query dynamically in Excel? thanks
Hi Lawrence,
If you wanted to do this I would create a new column which would have the start date from 3 years ago. I would then filter the table to where the current date >= the Date from 3 years ago.
To create the column you could use the following code in a new custom column in Power Query.
Date.StartOfYear(Date.From(DateTime.LocalNow()))