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

Luthius

Active Member
Joined
Apr 5, 2011
Messages
306
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.

StartEnd
01/01/202001/01/2021


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

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,066
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
Rich (BB code):
// 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

Rich (BB code):
// 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

cslendar.png
 
Last edited:

Luthius

Active Member
Joined
Apr 5, 2011
Messages
306
Rich (BB code):
// 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

Rich (BB code):
// 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
ParameterValueDateYearMonthDayQuarterWeek of YearWeek of MonthMonth NameDay Name
StartDate27/07/202010/02/20202020210173FebruaryMonday
EndDate31/07/202011/02/20202020211173FebruaryTuesday
12/02/20202020212173FebruaryWednesday
13/02/20202020213173FebruaryThursday
14/02/20202020214173FebruaryFriday
15/02/20202020215173FebruarySaturday
16/02/20202020216173FebruarySunday
17/02/20202020217184FebruaryMonday
18/02/20202020218184FebruaryTuesday
19/02/20202020219184FebruaryWednesday
20/02/20202020220184FebruaryThursday
21/02/20202020221184FebruaryFriday
22/02/20202020222184FebruarySaturday
23/02/20202020223184FebruarySunday
24/02/20202020224195FebruaryMonday
25/02/20202020225195FebruaryTuesday
26/02/20202020226195FebruaryWednesday
27/02/20202020227195FebruaryThursday
28/02/20202020228195FebruaryFriday
29/02/20202020229195FebruarySaturday

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

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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

Active Member
Joined
Apr 5, 2011
Messages
306
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.)



ParameterValue
StartDate01/01/2020
EndDate01/01/2021
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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


@03 - this is Query not a table
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
306
I started it again following your steps
My Parameters table is a Named Range

1595857851906.png


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


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


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

1595858032806.png
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
306
you didn't read my posts :eek:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top