WB Power Query Script for 'rolling calendar'

Started by stanl, June 04, 2018, 02:23:22 PM

Previous topic - Next topic

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
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"$"
   
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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

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

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
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"$"
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

ChuckC

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

td

"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Quote from: ChuckC on June 06, 2018, 06:01:45 AM
Channeling a bit of Marty, perhaps?  I recall some truly epic typos in support postings from a past era...

Like the 32But processor  :o