Author Topic: WB Power Query Script for 'rolling calendar'  (Read 439 times)

stanl

  • Pundit
  • *****
  • Posts: 794
WB Power Query Script for 'rolling calendar'
« on: June 04, 2018, 02:23:22 pm »
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

  • Tech Support
  • *****
  • Posts: 2595
    • WinBatch
Re: WB Power Query Script for 'rolling calendar'
« Reply #1 on: June 04, 2018, 02:47:32 pm »
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
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"$"

 
   
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


stanl

  • Pundit
  • *****
  • Posts: 794
Re: WB Power Query Script for 'rolling calendar'
« Reply #2 on: June 04, 2018, 05:29:47 pm »
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
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
f=f:'    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Month", "Start of Week", each Date.StartOfWeek([Column1],Day.%day%), type date),':@LF
 



td

  • Tech Support
  • *****
  • Posts: 2595
    • WinBatch
Re: WB Power Query Script for 'rolling calendar'
« Reply #3 on: June 05, 2018, 07:01:20 am »
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
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"$"
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


stanl

  • Pundit
  • *****
  • Posts: 794
Re: WB Power Query Script for 'rolling calendar'
« Reply #4 on: June 05, 2018, 11:24:16 am »
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

  • Tech Support
  • *****
  • Posts: 2595
    • WinBatch
Re: WB Power Query Script for 'rolling calendar'
« Reply #5 on: June 05, 2018, 11:38:54 am »
 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.
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


ChuckC

  • Full Member
  • ***
  • Posts: 155
Re: WB Power Query Script for 'rolling calendar'
« Reply #6 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...

td

  • Tech Support
  • *****
  • Posts: 2595
    • WinBatch
Re: WB Power Query Script for 'rolling calendar'
« Reply #7 on: June 06, 2018, 07:15:12 am »
It makes me wonder sometimes.
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


stanl

  • Pundit
  • *****
  • Posts: 794
Re: WB Power Query Script for 'rolling calendar'
« Reply #8 on: June 06, 2018, 11:12:00 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