PowerQuery Dynamic Date Dimension Table Filtering
in Power BI Desktop , PowerQuery Excel
I really like the new CALENDAR() function in DAX and the ability to create a dynamic Date table. There is a nice trick that you can do to mimic this behavior when you import a Date table though “Get Data” in Power BI Desktop or or Power Query Excel.
( Hopefully Chris Webb, Ken Plus and the other Powerquery experts will be kind and also offer a more robust solution if they haven’t already. Basically all the limited knowledge in PQ I have is due to their posts)
We import two tables , a sales ( fact ) table and a date table.
Btw I used the new really cool feature in December update “Enter Data” in Power BI Desktop.
Next we create two new queries for the max and the min date of the sales table that we will use to dynamically filter the date table.
Select Get Data-> Blank Query
and enter the following
= List.Min(Sales[Sales Date])
in the formula bar..where Sales is the name of the query of the sales table and the [Sales Date] is the name of the column containing dates that we like to filter the Date table. It will return a record with the first/min date in this column.
Rename the query to FirstDate or anything you prefer.
Now we need to repeat the same steps and create a new query that will return the last/max date.
Create again a blank query and enter this time
= List.Max(Sales[Sales Date])
and rename the query to LastDate or similar.
and now the fun part. Edit the Date table query in the editor ( if you haven’t import one you can import it now )
Select the Date column and click the arrow in the upper right corner of the column and filter the dates between two random dates, we are going to change them later.
Now in the formula bar you see the applied M code
= Table.SelectRows(#”Changed Type”, each [Date] >= #date(2016, 1, 1) and [Date] <= #date(2016, 1, 5) )
and the table filtered by the random dates we entered.
Now change the hard coded dates in the M code above in the formula bar with the names of the queries we created to retrieve the min and max date of the sale table. ( FirstDate , LastDate )
= Table.SelectRows(#”Changed Type”, each [Date] >= FirstDate and [Date] <= LastDate )
You will notice that the Date table dynamic filtered to the min a max dates of the sales table.
Load your queries “Close & Apply” to the data model and you are ready.
* Since you won’t need FirstDate & LastDate queries in the data model you can disable load by right clicking the queries and unselect the “Load to Report” box in the pop up properties window
Everytime you refresh the data model it will dynamically filter the date table to contain only the range of dates from your sales table.
One nice side effect is that the date slicers are not containing dates/years in the future which makes the reports looks better.
The cool thing is that it can be used to other scenarios that you need to dynamic filter a table based on another table value.
I wish you happy holidays and special thanks to the Power BI Team for the amazing year.