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

%3CLINGO-SUB%20id%3D%22lingo-sub-2242629%22%20slang%3D%22en-US%22%3EHow%20to%20create%20a%20list%20of%20dates%20(only%20start%20date%20of%20month%20required)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2242629%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Sir%2C%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20dates%3A-%3C%2FP%3E%3CP%3E1.%2001-Jan-21%20to%2025-Mar-21%26nbsp%3B%3C%2FP%3E%3CP%3E2.%2015-Feb-21%20to%2024-May-21%3C%2FP%3E%3CP%3Eand%20so%20on.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20have%20a%20list%20of%20dates%20(in%20Power%20Query%20only%2C%20preferably%20through%20PQ%20Interface)%20where%20the%20dates%20shall%20be%20as%20follows%3A-%3C%2FP%3E%3CP%3Efor%20S.%20No.1%20above%3A-%26nbsp%3B%2001-Jan-21%2C%2001-Feb-21%20and%2001-Mar-21%3C%2FP%3E%3CP%3Efor%20S.%20No.2%20above%3A-%26nbsp%3B%2015-Feb-21%2C%2001-Mar-21%2C%2001-Apr-21%20and%2001-May-21.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20means%20that%20for%20the%20%22From%20date%22%20column%2C%20i%20have%20to%20pick%20the%20%22From%20date%22%20only.%3C%2FP%3E%3CP%3EThen%20this%20shall%20be%20followed%20by%20start%20dates%20of%20each%20%26amp%3B%20every%20month%20till%20the%20last%20month%20of%20%22End%20date%22%20arrives.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20have%20%22Start%20date%20of%20the%20month%22%20of%20the%26nbsp%3B%20%22end%20date%22.%3C%2FP%3E%3CP%3EAlthough%20List.Generate%20helps%20but%20it%20is%20not%20understood%20by%20me%20at%20all.%3C%2FP%3E%3CP%3EKindly%20help%20please.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2242629%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2244174%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20list%20of%20dates%20(only%20start%20date%20of%20month%20required)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2244174%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1011649%22%20target%3D%22_blank%22%3E%40sandeepv12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20you'd%20like%20to%20have%20result%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20210px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F268202i0052A7F5FBD64126%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%3C%2FP%3E%0A%3CP%3E-%20reference%20the%20source%20table%20and%20keep%20only%20first%20row%3C%2FP%3E%0A%3CP%3E-%20reference%20it%20again%2C%20transform%20to%20Start%20of%20the%20Month%2C%20remove%20duplicates%20and%20remove%20first%20row%3C%2FP%3E%0A%3CP%3E-%20append%20second%20to%20first%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

@sandeepv12 

Assuming you'd like to have result like this

image.png

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

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,

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

image.png

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"