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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1661856218.png/d80b5b031741097f6f5c7f058abb8688.jpg)
There are other ways I could have filtered my Date column. I start by taking a duplicate copy of the Start Date query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1661856247.png/538b839bbb29a29cd941b763a712d292.jpg)
This time, I am only going to keep the ‘Source’ step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1661856271.png/d366d540e3111c7f736ff0ca0df0455e.jpg)
On the Transform tab, if I have Date selected, there is an option in the Date dropdown to transform the ‘Earliest’ date:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1661856292.png/bda29ef86f4261a8c352245e3a69cf4a.jpg)
This transforms my query into the earliest date:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1661856322.png/e2d449ef4e37c73f16c3ab2ba28b3165.jpg)
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’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1661856348.png/89469985e8caca3535afa5d02f8f9f33.jpg)
There is another useful option on the Transform tab, this time in the Statistics dropdown:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1661856375.png/4646efd4999f5d4c2cf819421afe8776.jpg)
I transform to the Minimum:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1661856406.png/419fabc4eb22376ee804f3ace053a119.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1661856439.png/b0743f7011453337556666356394840f.jpg)
I delete the ‘Calculated Minimum’ step and go back to the Statistics dropdown in the Transform tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1661856470.png/efdc9f0cf8de19159a63af5007f3c94d.jpg)
This time I transform the query to the Maximum value in Date:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1661856491.png/47d5c52214abc3c80bb4a091421c0da4.jpg)
I have my start and end date values expressed as numbers ready to create a list of dates to create my Date Table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1661856520.png/959346d3826c98ad88132bc052da2f23.jpg)
Since I started off with two queries that had six [6] steps each:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1661856547.png/86d480e2361359493fba9641723bab80.jpg)
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!