How to use existing date column to create a calendar in power query editor

[aatish178]

Hi All,

I already have a table loaded in power query. That table is having salesdate column.

I want to use this salesdate column to create a calendar table in power query itself.

Can someone plz help me know how can I use existing column to create a date range in power query as a seperate calendar table

[aatish178]

Till now I had just seen people are hardcoding start date and end date in parameter and then generating list of dates between 2 parameters using power query M language, but here I want to refer a existing salesdate column to find out min and Max in power query and then generating list of dates using that

[Anonymous · Accepted solution]

Hi @aatish178 

You can refer to the following solution.

Sample data

Based on the table, you can create a blank query and put the following code to advanced editor in power query.

let
    Query1 = let
   min_date=List.Min(Table[Salesdate]),
   max_date=List.Max(Table[Salesdate])
in
    List.Dates(min_date,Duration.Days(max_date-min_date)+1,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Query1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.