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


DateYearMonthMonth NameQuarterWeek of YearWeek of MonthDayDay of WeekDay of YearDay Name


29/07/2020
2020
7July
3
31
5
29
2
211Wednesday


30/07/2020
2020
7July
3
31
5
30
3
212Thursday


31/07/2020
2020
7July
3
31
5
31
4
213Friday