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
Gaurav Lakhotia
Gaurav holds an MBA in Business Analytics. As a data geek and avid learner, he has earned accolades including Microsoft Certified Data Analyst, Microsoft Certified Azure Administrator, and active Power BI Community contributor. He is also a Microsoft Certified Trainer. With critical thinking and project delivery skills, he strives to achieve customer delight in Data Analytics projects. Gaurav loves travelling with friends when he is not busy working on data.
