How to create a list of dates (only start date of month required)

New Contributor

Dear Sir,

I have a list of dates:-

1. 01-Jan-21 to 25-Mar-21 

2. 15-Feb-21 to 24-May-21

and so on.

I would like to have a list of dates (in Power Query only, preferably through PQ Interface) where the dates shall be as follows:-

for S. No.1 above:-  01-Jan-21, 01-Feb-21 and 01-Mar-21

for S. No.2 above:-  15-Feb-21, 01-Mar-21, 01-Apr-21 and 01-May-21.


This means that for the "From date" column, i have to pick the "From date" only.

Then this shall be followed by start dates of each & every month till the last month of "End date" arrives.

I would like to have "Start date of the month" of the  "end date".

Although List.Generate helps but it is not understood by me at all.

Kindly help please.

3 Replies


Assuming you'd like to have result like this


you may

- reference the source table and keep only first row

- reference it again, transform to Start of the Month, remove duplicates and remove first row

- append second to first


Please check in attached.

@Sergei Baklan 

Dear  Sergei,

My apologies as i think i have not given you the proper data set i am working upon.

Please find enclosed herewith the link:-!AvxCAQjXkctcgqUaRQHcxbqKo81_Uw?e=YFh5lW

There is an "Input Table" my actual data set and a  "Output Table" which is my desired data output through power query.

Kindly suggest the solution. If it can be done through List.generate only, i will be thankful to you, if you could explain me the solution in brief detail.



Here I'd add some coding, see entire script below and attached. One more point - you use time 24:00, that is actually 00:00:00 on next day. Thus we need to do some transformations here as well.


Scrip is

    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    #"FromTime Plus 1 Day" = Table.TransformColumns(
        {{"From Time", each _ + 1, type number}}
    #"ToTime Plus 1 Day" = Table.TransformColumns(
        #"FromTime Plus 1 Day",
        {{"To Time", each _ + 1, type number}}
    #"Changed Type" = Table.TransformColumnTypes(
        #"ToTime Plus 1 Day",
            {"From date", type date},
            {"To date", type date},
            {"From Time", type datetime},
            {"To Time", type datetime},
            {"Qtm", Int64.Type}
    #"List of Months to Add" = Table.AddColumn(
        #"Changed Type",
                Date.Year([To date])*100+Date.Month([To date]) -
                (Date.Year([From date])*100+Date.Month([From date]))
            ) ,
        type number
    #"Add Months" = Table.AddColumn(
        #"List of Months to Add",
                d=[From date],
                m = List.Transform(
                    each Date.StartOfMonth(Date.AddMonths(d, _))
    #"Add Start Date" = Table.AddColumn(
        #"Add Months",
        "List of dates",
        each List.Union({{[From date]},[Custom]})
    #"Removed Other Columns" = Table.SelectColumns(
        #"Add Start Date",
        {"From date", "To date", "From Time", "To Time", "Qtm", "List of dates"}
    #"Expanded List of dates" = Table.ExpandListColumn(
        #"Removed Other Columns",
        "List of dates"
    #"As dates" = Table.TransformColumnTypes(
        #"Expanded List of dates",
        {{"List of dates", type date}}
    #"As dates"