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.