WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: stanl on June 04, 2018, 02:23:22 PM

Title: WB Power Query Script for 'rolling calendar'
Post by: stanl 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.
Title: Re: WB Power Query Script for 'rolling calendar'
Post by: td 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) 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"$"
   
Title: Re: WB Power Query Script for 'rolling calendar'
Post by: stanl 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) 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



Title: Re: WB Power Query Script for 'rolling calendar'
Post by: td 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) 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"$"
Title: Re: WB Power Query Script for 'rolling calendar'
Post by: stanl 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),
Title: Re: WB Power Query Script for 'rolling calendar'
Post by: td 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.
Title: Re: WB Power Query Script for 'rolling calendar'
Post by: 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...
Title: Re: WB Power Query Script for 'rolling calendar'
Post by: td on June 06, 2018, 07:15:12 AM
It makes me wonder sometimes.
Title: Re: WB Power Query Script for 'rolling calendar'
Post by: stanl on June 06, 2018, 11:12:00 AM
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