Power Query: Check the Filters - Part 2
31 August 2022
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  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  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!