Function to generate list of dates

Question

How to generate a list of all dates between several years?

This is needed to calculate depreciation for each day or month or year by filtering such a table by date and applying daily (monthly) depreciation.

Answer 1

I've created a function to generate such a table. It takes start and end years as parameters.

You can place it either as a separate query and call later, or fully include in your query.

let
    GenerateDates = (StartYear as number, EndYear as number) =>

    let
        //Generate a list of years
        Yrs = #table(type table [J = Int64.Type, Yr = Int64.Type], List.Transform({StartYear..EndYear}, each {1, _})),
        //Generate a list of months
        Mnths = #table(type table [J = Int64.Type, Mn = Int64.Type], List.Transform({1..12}, each {1, _})),
        //Generate a list of days
        Days = #table(type table [J = Int64.Type, D = Int64.Type], List.Transform({1..31}, each {1, _})),
        //Join all these tables
        Join1 = Table.Join(Yrs, "J", Mnths, "J", JoinKind.Inner),
        Join2 = Table.Join(Join1, "J", Days, "J", JoinKind.Inner),
        // Add column with date, using try..otherwise to evade errors for non-existing dates, such as 31 april. :)
        AddDate = Table.AddColumn(Join2, "Date", each try #date([Yr], [Mn], [D]) otherwise null, type date),
        //Get rid of nulls
        FilterNulls = Table.SelectRows(AddDate, each [Date] <> null),
        //Return result
        Result = Table.SelectColumns(FilterNulls, {"Date"})
    in
        Result
in
    GenerateDates

Update.

Although, as MarcelBeug advises, it is easier to use List.Dates (which I've missed :) ), here are some examples of using type table that might be useful.

Answer 2

You can use List.Dates(start as date, count as number, step as duration).

e.g List.Dates(#date(2016,1,1),366,#duration(1,0,0,0)) gives you all dates for 2016.