How to create a custom PQ Calendar using Start and End date parameters cells

[Luthius]

Guys, I would like to know how to create a custom Power Query Calendar using Start and End date parameters based in my cells values.
The values in my worksheet can be changed by the user. The calendar will be generated based on the table Start and End Information.

Start	End
01/01/2020	01/01/2021

This calendar table I will use on another data table that I will transform.

 

[alansidman]

What do you want your end result to look like? Show us a sample of data with a shorter period of time. Perhaps only 10 days.

 

[sandy666]

// fnGetParameter
(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

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

    Source = {startdate..enddate},
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Type = Table.TransformColumnTypes(ToTable,{{"Column1", type date}}),
    Rename = Table.RenameColumns(Type,{{"Column1", "Date"}}),
    Year = Table.AddColumn(Rename, "Year", each Date.Year([Date]), Int64.Type),
    Month = Table.AddColumn(Year, "Month", each Date.Month([Date]), Int64.Type),
    Day = Table.AddColumn(Month, "Day", each Date.Day([Date]), Int64.Type),
    Quarter = Table.AddColumn(Day, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    WeekOfYear = Table.AddColumn(Quarter, "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    WeekOfMonth = Table.AddColumn(WeekOfYear, "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    MonthName = Table.AddColumn(WeekOfMonth, "Month Name", each Date.MonthName([Date]), type text),
    DayName = Table.AddColumn(MonthName, "Day Name", each Date.DayOfWeekName([Date]), type text)
in
    DayName

 

[Luthius]

// fnGetParameter
(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

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

    Source = {startdate..enddate},
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Type = Table.TransformColumnTypes(ToTable,{{"Column1", type date}}),
    Rename = Table.RenameColumns(Type,{{"Column1", "Date"}}),
    Year = Table.AddColumn(Rename, "Year", each Date.Year([Date]), Int64.Type),
    Month = Table.AddColumn(Year, "Month", each Date.Month([Date]), Int64.Type),
    Day = Table.AddColumn(Month, "Day", each Date.Day([Date]), Int64.Type),
    Quarter = Table.AddColumn(Day, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    WeekOfYear = Table.AddColumn(Quarter, "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    WeekOfMonth = Table.AddColumn(WeekOfYear, "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    MonthName = Table.AddColumn(WeekOfMonth, "Month Name", each Date.MonthName([Date]), type text),
    DayName = Table.AddColumn(MonthName, "Day Name", each Date.DayOfWeekName([Date]), type text)
in
    DayName

Parameter	Value		Date	Year	Month	Day	Quarter	Week of Year	Week of Month	Month Name	Day Name
StartDate	27/07/2020		10/02/2020	2020	2	10	1	7	3	February	Monday
EndDate	31/07/2020		11/02/2020	2020	2	11	1	7	3	February	Tuesday
			12/02/2020	2020	2	12	1	7	3	February	Wednesday
			13/02/2020	2020	2	13	1	7	3	February	Thursday
			14/02/2020	2020	2	14	1	7	3	February	Friday
			15/02/2020	2020	2	15	1	7	3	February	Saturday
			16/02/2020	2020	2	16	1	7	3	February	Sunday
			17/02/2020	2020	2	17	1	8	4	February	Monday
			18/02/2020	2020	2	18	1	8	4	February	Tuesday
			19/02/2020	2020	2	19	1	8	4	February	Wednesday
			20/02/2020	2020	2	20	1	8	4	February	Thursday
			21/02/2020	2020	2	21	1	8	4	February	Friday
			22/02/2020	2020	2	22	1	8	4	February	Saturday
			23/02/2020	2020	2	23	1	8	4	February	Sunday
			24/02/2020	2020	2	24	1	9	5	February	Monday
			25/02/2020	2020	2	25	1	9	5	February	Tuesday
			26/02/2020	2020	2	26	1	9	5	February	Wednesday
			27/02/2020	2020	2	27	1	9	5	February	Thursday
			28/02/2020	2020	2	28	1	9	5	February	Friday
			29/02/2020	2020	2	29	1	9	5	February	Saturday

Wow. Advanced approach. Is possible you share this file so I can view it working?
Thanks in advance. Wow again. Lol

 

[sandy666]

LOL, enough to create two blank queries, paste codes there, rename suitable next add table with start/end dates with headers as you can see and name this table: Parameters
names of queries are after //
remember that Power Query is case sensitive

 

[Luthius]

I followed your steps and had an additional precaution with Case sensitive names but is returning an error (Expression.Error: The name 'fnGetParameter' wasn't recognized. Make sure it's spelled correctly.)
My steps were:
01-New Blank WorkBook and saved it as Advanced-Calendar-With-Power-Query.xlsx
02-Create a table as named range table as Parameter =Sheet1!$A$1:$B$3
03-On PQ I created a blank query where I paste the function code and called this table as Parameters
04-I created another blank query where I paste the function for generate the calendar table. On this step the error is showing up (The name 'fnGetParameter' wasn't recognized. Make sure it's spelled correctly.)

Parameter	Value
StartDate	01/01/2020
EndDate	01/01/2021

 

[sandy666]

Parameters is the name of the table you show in your post
on my picture you have the names of queries

@03 - this is Query not a table

 

[Luthius]

I started it again following your steps
My Parameters table is a Named Range

My query table were named correctly as you can see below:

This was my Calendar Query using the code of your post.

And here the error that is showing on Calendar Query table:

 

[sandy666]

My Parameters table is a Named Range

you didn't read my posts
it should be a Table not NamedRange

 

[Luthius]

you didn't read my posts
it should be a Table not NamedRange

View attachment 19072

Lol,I wrote as part of my step#2

I was trying by using named range optimize resources.
Now is working perfectly.
Thank you very much. I will study more about power query. This is amazing. I guess VBE is couting down its end.