WB Power Query Script for 'rolling calendar'

[stanl]

Having made lookup calendar tables for years the attached is not necessarily a better mousetrap. But I am exploring the versatility of PQ [and also looking for work as my position at Charter/TWC was moved to Stamford, CT] and PQ/Power BI comes up a lot on the radar for Analyst jobs....

Review the script header if you wish to play with it.

[td]

I suppose I should give MSFT some money and upgrade my workstation's version of Office or break down and switch to 360... They just dropped $7.5 B on Github so maybe they are feeling a bit short on spare change.

BTW, I think you could put your multiple line queries into a multiline string - particularly since they accept line-feed line separators.  Maybe quicker to type and a little easier to read.

I may have missed a quote or two but something roughly like this:

Code (winbatch) Select Expand

f=$"let
    Source = #date(%year%,1,1),
    Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow()) - Number.From(Source), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Inserted Year" = Table.AddColumn(#"Converted to Table", "Year", each Date.Year([Column1]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Column1]), Int64.Type),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Month", "Start of Week", each Date.StartOfWeek([Column1]), type date),
    #"Inserted Day" = Table.AddColumn(#"Inserted Start of Week", "Day", each Date.Day([Column1]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Column1]), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Day Name",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type datetime}})
    in
    #"Changed Type"$"
   

[stanl]

First thing I tried was the multi-line:   need to of course use ##and the word 'in'  appears to be reserved in WB and I couldn't get around that. Also, using the @LF is required. In fact once I had run a successful script I went into the Advanced PQ editor and re-copied the code out to both a text file (so I could use FileGet() which is how I used to handle long SQL statement) or tried to manually insert into another blank workbook, both of which failed miserably.

[EDIT]

Completely missed your mention of multiline handling LF. After some coffee I will give WB's multi-line another shot. Another interesting feature: Excel assumes the start of the week is Sunday, but if you modify the script and add a day variable at the beginning

Code (WINBATCH) Select Expand


year=2018 ; choose a starting year
day="Monday" ;choose start of week


then just change the line that creates the start of week

Code (WINBATCH) Select Expand


f=f:'    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Month", "Start of Week", each Date.StartOfWeek([Column1],Day.%day%), type date),':@LF


[td]

I completely forgot about escaping the pound/hash/number character.  We made it a character requiring an escape after much debate.  Ultimately script performance won out over convenience but I am still not completely satisfied with the solution.

The "in" should not make any difference as long as you are using a recent version of WinBatch.   WinBatch and/or the WIL interpreter standardizes script line breaks to line-feed.  This includes line breaks in multiline strings embedded into scripts.  However, functions like FilePut standardize line breaks to the Windows text file standard carriage-return / line-feed pair.

This passes syntax checking but may need some additional tweaking someplace to make Excel happy:

Code (winbatch) Select Expand

f=$"let
    Source = ##date(%year%,1,1),
    Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow()) - Number.From(Source), ##duration(1,0,0,0)),
    ##"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ##"Inserted Year" = Table.AddColumn(##"Converted to Table", "Year", each Date.Year([Column1]), Int64.Type),
    ##"Inserted Month" = Table.AddColumn(##"Inserted Year", "Month", each Date.Month([Column1]), Int64.Type),
    ##"Inserted Start of Week" = Table.AddColumn(##"Inserted Month", "Start of Week", each Date.StartOfWeek([Column1]), type date),
    ##"Inserted Day" = Table.AddColumn(##"Inserted Start of Week", "Day", each Date.Day([Column1]), Int64.Type),
    ##"Inserted Day Name" = Table.AddColumn(##"Inserted Day", "Day Name", each Date.DayOfWeekName([Column1]), type text),
    ##"Renamed Columns" = Table.RenameColumns(##"Inserted Day Name",{{"Column1", "Date"}}),
    ##"Changed Type" = Table.TransformColumnTypes(##"Renamed Columns",{{"Date", type datetime}})
    in
    ##"Changed Type"$"

[stanl]

Thanks. Attached with your multi-line (I added the variable start of work) and original code commented at end of script. The script header says it is WB 2018B, and 64 bit Office. Out of curiosity if someone runs this with Office 32bit with the power query addin enabled, assume lines like

##"Inserted Year" = Table.AddColumn(##"Converted to Table", "Year", each Date.Year([Column1]), Int64.Type),

would need to be changed to

##"Inserted Year" = Table.AddColumn(##"Converted to Table", "Year", each Date.Year([Column1]), Int32.Type),

[td]

 In my ongoing effort to completely confuse WinBatch users I mistyped again.  I typed  FilePut standardizes to carriage-return/line-feed pair.  I meant to type FileWrite standardizes to carriage-return/line-feed pair instead.

[ChuckC]

Channeling a bit of Marty, perhaps?  I recall some truly epic typos in support postings from a past era...

[td]

It makes me wonder sometimes.

[stanl]

Channeling a bit of Marty, perhaps?  I recall some truly epic typos in support postings from a past era...

Like the 32But processor  :o