Dynamic FILTER BETWEEN YEARS using Power Query and Data Validation

Weclome to Excel Jet Consult blog post. In this Excel tutorial, we will learn how to create a dynamic filter between years using the Power Query Snowflake Merging & Data Validation. Let’s get started

Sample Data

In the caption below, we have a transaction data formatted as an Excel Table and named transaction_facttbl.

Create Helper Column

Based on the dataset, we have Date in column A whereas we want to Filter between Years, not Date. Hence, there is a need to create an helper column. To do that,

  • Right-click on Column B and deploy CTRL + SHIFT + =
  • Type in Year as the header
  • In cell B2, deploy =YEAR([@Date])
  • Click CTRL + Enter
  • Deploy CTRL + SHIFT + Down Arrow and CTRL + Backspace
  • In the Number group of the Home tab, select General from the dropdown list. This would display the extracted Years as unformatted number. See caption below

Extract & Sort Unique Years

In the Result sheet, we want to use two dynamic array functions – SORT & UNIQUE Functions, to extract and sort the data in ascending order. Therefore,

  • In cell R2, deploy =SORT(UNIQUE(Transaction_facttbl[Year])) – transaction_facttbl is the table name while [Year] is the column we extracted from and sorted.
  • Click CTRL + Enter.

In the caption below, we have the list of the unique years

List Data Validation

Next, in cells A2 and B2 of the Result sheet, we want to create two list data validation to handle the extracted years in column R. Therefore,

  • In cell A2, in the Data Tool group of the Data Tab, click on Data Validation.
  • In the Data Validation dialogue box, select List from the Allow dropdown.
  • For the Source, select data from cell R2 to the last row.

  • Click OK
  • Copy the data validation in cell A2 and Paste in cell B2
  • Select Year 2006 and 2007 in cells A2 and B2 respectively

Load Data to Power Query

Next, we are going to load the dataset, transaction_facttbl into the Power Query. Therefore,

  • Select a single cell in the dataset and in the Get & Transform Data group of the Data tab, select From Table/Range

The dataset in loaded into Power Query as seen below

Note, I have transformed the data by changing the Date column Data Type from DateTime to Date.

Create Only Connection

Next, we want to Close & Load to to create connection. Therefore,

  • In the Home tab of the Power Query editor, to the far left, click on Close & Load and select Close & Load To
  • In the Import Data dialogue box, select Only Create Connection

  • Click OK

Load Start Year & End Year Into Power Query

Next, in the Result sheet, we want to get the Start Year and the End Year into the Power Query editor to create Snowflake Merging. Therefore,

  • Right-click any single cell within the Start and end Year
  • Select Get Data From Table/Range

 

Note, the above intermediate steps is only available in Office365/Microsoft365 for Excel. If you’re not on any of these, kindly select From Table/Range in Get & Transform Data group of the Data tab.

Custom Formula to Extract Years

In the Power Query editor, we have the small table of State Year and End Year as seen below

 

Next, we want to expand into new rows, the years between Start and End Years. To do that,

  • In the General group of the Add Column tab, select Custom Function
  • In the Custom Function dialogue box, provide Year Column as the column name
  • In the Custom Column Formula, execute the following:

={Number.From([Start Year])..Number.From([End Year])}

  • Click OK

We have List under the new Year Column as seen below

  • Double-click on the upward pointed arrows and select Expand to New Rows. 

We have the result below

Next,

  • In the Home tab click on Close and Load To
  • In the Import Data, select Only Create Connection
  • In the Queries & Connections task panes seen below

  • Double-click the transaction_facttbl – this would open the Power Query Editor

Snowflake Merging

Next, we want to create a snowflake merging between the main table (transaction_facttbl) and the lookup table (table2). Therefore,

  • In the Combine group of the Power Query Home tab, select Merge Queries & Merge as New respectively.
  • In the Merge dialogue box, select Year column
  • In the dropdown below, select Table2
  • Then select Year Column 
  • In the Join Kind drop-down, select Inner (only matching rows)

See the caption below for the few steps above

  • Click OK

Now, we have Merge1 query

  • Click on Close & Load and Close & Load To
  • Select Only Create Connection
  • In the Queries & Connections task pane, right-click on Merge1
  • Select Load To
  • In the Import Data dialogue box, select Table 
  • Select Existing worksheet and select any cell of choice

  • Click OK.

The final result is available in Excel Table

From the caption below, we can see that we result showing for Year 2006 and 2007 based on the selected Year in cells A2 and B2 respectively.

This works pretty fine. If we choose, for instance, Years 2010 and 2010 in the two drop-down, all we need to do is select a single cell in the Power Query result table, right-click and select refresh. The data will respond accordingly.

That is essentially how to dynamically filter between years using Power Query and Data Validation in Excel. I hope you enjoy the tutorial. You can watch the vide in the link above and remember to subscribe to my YouTube channel because, the Best is yet to come!

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *