Tuesday, August 4, 2020

Power Query - Table dCalendar

let
   Source = List.Dates,
//   Step01 = Source(#date(2019, 1, 1), Duration.Days(DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),1)) - #date(2019,1,1)), #duration(1, 0, 0, 0)),
   Step01 = Source(#date(2000, 1, 1), Duration.Days(DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),1)) - #date(2000,1,1)), #duration(1, 0, 0, 0)),

   Step02 = Table.FromList(Step01, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   Step03 = Table.AddIndexColumn(Step02, "Index", 1, 1),
   Step04 = Table.RenameColumns(Step03,{{"Column1", "Date"}}),
   Step05 = Table.AddColumn(Step04, "Year", each Date.Year([Date])),
   Step06 = Table.AddColumn(Step05, "Month Number", each Number.ToText(Date.Month([Date]),"00")),
   Step07 = Table.AddColumn(Step06, "Day", each Number.ToText(Date.Day([Date]),"00")),
   Step08 = Table.AddColumn(Step07, "Day Name", each Date.ToText([Date],"ddd")),
   Step09 = Table.AddColumn(Step08, "Month Name", each Date.ToText([Date],"MMM")),
   Step10 = Table.AddColumn(Step09, "Quarter Number", each Date.QuarterOfYear([Date])),

   Step11 = Table.AddColumn(Step10, "Short Year", each Date.Year([Date])),
   Step12 = Table.TransformColumnTypes(Step11,{{"Short Year", type text}}),
   Step13 = Table.SplitColumn(Step12,"Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
   Step14 = Table.TransformColumnTypes(Step13,{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
   Step15 = Table.RemoveColumns(Step14,{"Short Year.1"}),
   Step16 = Table.RenameColumns(Step15,{{"Short Year.2", "Short Year"}}),

   Step17 = Table.AddColumn(Step16, "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
   Step18 = Table.AddColumn(Step17, "StartWeek", each Date.StartOfWeek([Date])),
   Step19 = Table.AddColumn(Step18, "YYYYMM", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.Month([Date]),"00")),

   Step20n = Table.AddColumn(Step19, "YYYYWeekYear", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.WeekOfYear([Date], Day.Saturday),"00")),

   Step20 = Table.AddColumn(Step20n, "WeekDay", each Number.ToText(Date.DayOfWeek([Date], Day.Saturday),"00")),
   Step21 = Table.AddColumn(Step20, "YYYYMMDD", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.Month([Date]),"00") & Number.ToText(Date.Day([Date]),"00")),
   Step22 = Table.AddColumn(Step21, "MMDD", each  Number.ToText(Date.Month([Date]),"00") & Number.ToText(Date.Day([Date]),"00")),
   Step23 = Table.TransformColumnTypes(Step22,{{"Date", type date}, {"Index", Int64.Type}, {"Year", Int64.Type}, {"Month Number", Int64.Type}, {"Day", Int64.Type}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter Number", Int64.Type}, {"Short Year", Int64.Type}, {"Quarter Year", type text}, {"StartWeek", type date}, {"YYYYMM", Int64.Type}, {"WeekDay", Int64.Type}, {"YYYYMMDD", Int64.Type}}),
   Step24 = Table.SelectRows(Step23, each true),
   Step25 = Table.Sort(Step24,{{"YYYYMMDD", Order.Descending}}),
   Step26 = Table.AddColumn(Step25, "WeekName", each Number.ToText([WeekDay]) & " - " & [Day Name]),
   Step27 = Table.AddColumn(Step26, "QtdDias", each DateTime.Date(Date.AddDays(DateTime.FixedLocalNow(),1)) - [Date]),
   Step28 = Table.AddColumn(Step27, "WeekYear", each Date.WeekOfYear([Date])),
   Step29 = Table.TransformColumnTypes(Step28,{{"QtdDias", Int64.Type}, {"Date", type date}, {"Index", Int64.Type}, {"Year", Int64.Type}, {"Month Number", Int64.Type}, {"Day", Int64.Type}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter Number", Int64.Type}, {"Short Year", Int64.Type}, {"Quarter Year", type text}, {"StartWeek", type date}, {"YYYYMM", Int64.Type}, {"WeekDay", Int64.Type}, {"YYYYMMDD", Int64.Type}, {"MMDD", type text}, {"WeekName", type text}, {"WeekYear", Int64.Type}}),
   Step30 = Table.SelectRows(Step29, each ([WeekName] <> "0 - Sat" and [WeekName] <> "1 - Sun"))
in
    Step30

No comments:

Post a Comment

Power BI APP - Delete or Unpublish

 UNPUBLISH DELETE