Power Query: Check the Filters - Part 2

Welcome to our Power Query blog.  This week, I look at how the options on the Transform tab can filter a query, saving both time and steps.

Last time, I looked at an example where I was creating a Date Table, where I started by finding the start and end date of the data.  I showed how I could save time and steps by filtering the Date column to find the rows with the earliest date. 

There are other ways I could have filtered my Date column.  I start by taking a duplicate copy of the Start Date query:

This time, I am only going to keep the ‘Source’ step. 

On the Transform tab, if I have Date selected, there is an option in the Date dropdown to transform the ‘Earliest’ date:

This transforms my query into the earliest date:

If I could use dates to create lists, I could stop here, but unfortunately Power Query is not able to do this (yet!).  Instead, I delete the ‘Calculated Earliest’ step and change the data type on Date to ‘Whole Number’:

There is another useful option on the Transform tab, this time in the Statistics dropdown:

I transform to the Minimum:

I have achieved my goal in only three [3] steps.  I rename the query Start Date in 3, and create a duplicate query which I call End Date in 3:

I delete the ‘Calculated Minimum’ step and go back to the Statistics dropdown in the Transform tab:

This time I transform the query to the Maximum value in Date:

I have my start and end date values expressed as numbers ready to create a list of dates to create my Date Table:

Since I started off with two queries that had six [6] steps each:

I have reduced the work needed by half by using the options available to filter a single column.

Come back next time for more ways to use Power Query!