Power Query: Recent Dates
18 November 2020
Welcome to our Power Query blog. This week, I show how to only extract the dataset for the last two years, say.
I have some accounting data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I only want to work with the data for the last two (2) years. I begin by extracting my data to Power Query using the ‘From Table’ option on the ‘Get & Transform’ section of the Data tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
Once I have my data, I change the datatype of my Date column to ‘Date’. I can do this from several places; here, I have used the ‘Data Type’ dropdown on the Transform tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
Since this change type step comes directly after the automatically generated change type step, I am invited to make the amendment to the existing step, which I accept.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I can then filter my column by using the down arrow next to the column title.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I have a dropdown from the ‘Date Filter’ option with a selection of different ways to filter my data. I choose ‘Custom Filter’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I want to choose the ‘Advanced’ options, so I select that box.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I change ‘Operator’ from ‘equals’ to ‘is in year’ and then look at my ‘Value’ dropdown.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I select ‘Last Year’ and then move onto the next line. I want to pick data from two years, so I change ‘And’ to ‘Or’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I have a choice here: I can either pick ‘This Year’ or ‘Year to Date’. This will depend upon my needs, but for accounting purposes, I will usually pick ‘Year to Date’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I can now apply this to my data. I started off with 999+ rows.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/207/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I now have 680 rows, and I can see that the earliest row is from last year. Since I haven’t specified any dates in my filter, this is a dynamic way to see the last two years.
Come back next time for more ways to use Power Query!