Dynamic Date Table in Power Query(Using Different Date)


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.


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 ))


=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.

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.