For the second time, a blog post as a call for help!
I have encountered a problem building a dynamic date table in Power Query. I will walk through the steps I went through to build the table in my solution.
Hopefully I can find out why isn’t it working as I had outlined???
I used the following table as the source data for the solution. It was a list of projects with milestones and deadlines.
I created a date table using the technique outlined in my previous post. This involved filtering the milestone deadline column to earliest and latest dates, then using these dates as reference points to generate a date table.
I built the date table and loaded it to a sheet in the workbook. So far so good.
I encountered the issue when I tried to update the source data. In this case, by adding a new project with a deadline in 2019 (later than the current end date in the date table).
When I tried to refresh the date table, I received the following error message:
The key didn’t match any rows in the table.
I opened the Query Editor to see where the error occurred in the applied steps.
It seems like the error occurs on the last drill down step.
There are a number of workarounds to this.
The start and end dates can come from a separate parameter table, as Ken Puls outlined in his blog post here.
The date range can be generated with relative date logic referencing the DateTime.LocalNow function.
I’m curious if there is a way to get this technique to work in Power Query. A way to generate start and end dates dynamically as the source data refreshes.
Any advice or references would be greatly appreciated!
UPDATE – THE SOLUTION
The problem I encountered was in the drill down. The method I was using was hard coding the contents of the single row instead of referencing the row itself.
= #"Removed Duplicates"{[#"Milestone Deadline"=#date(2018, 12, 31)]}[Milestone Deadline]
The solution was to introduce a date transformation step before the final drill down.
For the End Date, this transformation was to calculate the end of the year. For the Start Date, the beginning of the year.
Now that the end of the year has been calculated, I drilled down in the remaining cell.
The drill down after the transformation is relative to the first row, rather than the contents of the first row.
= #"Calculated End of Year"{0}[Milestone Deadline]
The date transformation applied step changes the drill down, it is now a relative reference.
Previously, the drill down was done explicitly:
{[#"Milestone Deadline"=#date(2018, 12, 31)]}
instead of explicitly referencing the contents of the row:
{0}
As it was delicately put to me:
Serves you right then. You shouldn’t be trimming your calendar table anyway. Lol!