Power Query: Recent Dates

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:

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.

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:

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.

I can then filter my column by using the down arrow next to the column title.

I have a dropdown from the ‘Date Filter’ option with a selection of different ways to filter my data. I choose ‘Custom Filter’:

I want to choose the ‘Advanced’ options, so I select that box.

I change ‘Operator’ from ‘equals’ to ‘is in year’ and then look at my ‘Value’ dropdown.

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’.

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’.

I can now apply this to my data. I started off with 999+ rows.

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!