Create a list of dates between a start and end date at an interval

[Seeget2]

I have users entering deliverables into a SharePoint list. The users enter start date, end date, and the Interval (weekly, monthly, quarterly...). 

To make this data useful for reporting I need to create a list of all of the delivery dates between the start and end date. I can create a list that shows everyday between the two dates {Number.From([StartDate])..Number.From([EndDate])}, but I need the list to only return dates using the specified interval, weekly(7), bi-weekly(14),monthly(30),quarterly(90).

I've scoured the internet for this, but I keep coming up short. Any help would be greatly appreciated. Thanks!

[watkinnc · Accepted solution]

You can do this:

=Table.AddColumn(PriorStepOrTableName, " Date List", each List.Dates([start date], Duration.Days([end date] - [start date]), [Interval]))

This is assuming that your interval column is integers.

--Nate

[Seeget2]

Thank you! I've been bogged down with other work so I'm just now getting back to this, but I believe this will work for me! 

[edhans]

Not sure of a great way to do this purely with lists. This code though will put it into a table and only keep dates that match quarter ends.

let
    Source = { Number.From(#date(2021,1,1))..Number.From(#date(2022,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "IsEndOfQuarter", each Date.EndOfQuarter([Date]) = [Date]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([IsEndOfQuarter] = true)),
    Date = #"Filtered Rows"[Date]
in
    Date

To do it only within lists, you'd need to use List.Generate to create the list of dates, checking each value for what you want and keeping it. It is far more involved using List.Generate.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.