PQ - Calendar with user defined StartDate and EndDate

[sandy666]

So, we will need one small Excel Table and two blank Queries

Parameter	Value
StartDate	29/07/2020
EndDate	31/07/2020

in Name Manager change the name to Parameters

first Query

(ParameterName as text) =>
    let
        ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
        ParamRow = Table.SelectRows(ParamSource, each ([Parameter]=ParameterName)),
            Value=
            if Table.IsEmpty(ParamRow)=true
                        then null
                        else Record.Field(ParamRow{0},"Value")
    in
        Value

change the name to fnGetParameter

second Query

let
    startdate = Number.From(fnGetParameter("StartDate")),
    enddate = Number.From(fnGetParameter("EndDate")), 

    Source = {startdate..enddate},
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), type table[Date = Date.Type], null, ExtraValues.Error),
    Type = Table.TransformColumnTypes(ToTable,{{"Date", type date}})
in
    Type

change the name to Calendar

this is a basic calendar with dates column only but it can be extended to months, days, years, weeks and so on

and extended result is

Date	Year	Month	Month Name	Quarter	Week of Year	Week of Month	Day	Day of Week	Day of Year	Day Name
29/07/2020	2020	7	July	3	31	5	29	2	211	Wednesday
30/07/2020	2020	7	July	3	31	5	30	3	212	Thursday
31/07/2020	2020	7	July	3	31	5	31	4	213	Friday