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

[sandeepv12]

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.

[#2]

@sandeepv12 

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.

[#3]

@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:-

https://1drv.ms/x/s!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.

Regards,

[#4]

@sandeepv12 

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

let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    #"FromTime Plus 1 Day" = Table.TransformColumns(
        Source,
        {{"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",
        "Months",
        each
            List.Numbers(
                1,
                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",
        "Custom",
        each
             [
                d=[From date],
                m = List.Transform(
                    [Months],
                    each Date.StartOfMonth(Date.AddMonths(d, _))
                )][m]
    ),
    #"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}}
    )
in
    #"As dates"