Agenda:
1. Problem Statements
2. Possible ways
3. Solution
1. Problem Statements
I want to create a dynamic date table using the date field from another table. I have a table called ‘Task Details’ with a column named ‘Date’.
2. Possible Ways
We have many possible ways. For instance, we can create a static table, set up parameters, and then modify them whenever needed. Additionally, there are numerous dynamic approaches. Let’s try one.
3.Solution
We have one table “Task Details”
Step 1:
Let’s create a normal date list using the query provided below for a ‘Dynamic Date Table’.
List.Dates(#date( 2023, 01, 01 ),5,#duration( 1, 0, 0, 0 ))
Step 2:
Let’s calculate the minimum date of the ‘Task Details[Date]’ column. To do that, create a blank query and use the following code to calculate the minimum date.
= List.Min(Task_Details[Date])
Same Step for Maximum date :
= List.Max(Task_Details[Date])
Now We have four queries
Step 3:
Now Lets replace the code of “Dynamic Date Table” From
= List.Dates(#date( 2023, 01, 01 ),5,#duration( 1, 0, 0, 0 ))
to
=List.Dates(List.Min(Task_Details[Date]),Number.From(List.Max(Task_Details[Date])-List.Min(Task_Details[Date]))+1,#duration( 1, 0, 0, 0 ))
Step 4:
Now we can delete the ‘Min Date’ and ‘Max Date’ queries as we have directly added the code to the Dynamic date table.
Step 5:
Then, in the Transform tab, select the ‘Convert to Table‘ option.
Now our dynamic date table is ready.
Conclusion: We can create Dynamic Date table in Power Query Editor in few simple steps.
Author By
Kamal Sharma
Kamal brings over 20 years of experience in data analytics and business intelligence. He has led the design and implementation of analytics solutions across operations, financial reporting, and performance improvement initiatives. With a background in business statistics and Six Sigma, his work focuses on applying data in a structured and practical way to solve real business challenges.
