//Metadata: 
//Programming language: Power Query M 
//Natural language: English 
//Output: date table 

//Explanation: 
//this code generates a European date calendar. Power Query can only generate American date calendars by default.
//the calender includes a relativeDate column

//How to use: 
//Insert this code into an empty query


let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMjDUByIjAwMDpdhYAA==", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [StartDate = _t]
  ), 
  #"ADD EndDate" = Table.AddColumn(Source, "EndDate", each Date.From(DateTime.LocalNow()), type date), 
  #"CHNG Type" = Table.TransformColumnTypes(#"ADD EndDate",{{"StartDate", type date}, {"EndDate", type date}}), 
  #"ADD Date" = Table.AddColumn(
    #"CHNG Type", 
    "Date", 
    each {Number.From([StartDate]) .. Number.From([EndDate])}
  ), 
  #"EXPD Date" = Table.ExpandListColumn(#"ADD Date", "Date"), 
  #"CHNG Type1" = Table.TransformColumnTypes(#"EXPD Date", {{"Date", type date}}), 
  #"REMV StartDate, EndDate" = Table.RemoveColumns(#"CHNG Type1", {"StartDate", "EndDate"}), 
  #"ADD Year" = Table.AddColumn(#"REMV StartDate, EndDate", "Year", each Date.Year([Date]),Int64.Type), 
  #"ADD Month" = Table.AddColumn(#"ADD Year", "Month", each Date.Month([Date]),Int64.Type), 
  #"ADD MonthName" = Table.AddColumn(#"ADD Month", "MonthName", each Date.MonthName([Date]), type text), 
  #"ADD ShortMonthName" = Table.AddColumn(
    #"ADD MonthName", 
    "ShortMonthName", 
    each Text.Start([MonthName], 3), type text
  ), 
  #"ADD Quarter" = Table.AddColumn(
    #"ADD ShortMonthName", 
    "Quarter", 
    each Date.QuarterOfYear([Date]),Int64.Type
  ),
    #"ADD QtrText" = Table.AddColumn(#"ADD Quarter", "QtrText", each "Qtr " & Text.From( [Quarter] )), 
  #"INST DayName" = Table.AddColumn(
    #"ADD QtrText", 
    "DayName", 
    each Date.DayOfWeekName([Date]), 
    type text
  ), 
  #"INST DayOfWeek European" = Table.AddColumn(
    #"INST DayName", 
    "DayOfWeek", 
    each Date.DayOfWeek([Date]) + 1, 
    Int64.Type
  ),
    //  ISO 8601 Week
    #"ADD Week European" = Table.AddColumn(
    #"INST DayOfWeek European", 
    "ISO Week", 
    each 
      let
        CurrentThursday = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday)), 
        YearCurrThursday = Date.Year(CurrentThursday), 
        FirstThursdayOfYear = Date.AddDays(
          #date(YearCurrThursday, 1, 7), 
          - Date.DayOfWeek(#date(YearCurrThursday, 1, 1), Day.Friday)
        ), 
        ISO_Week = Duration.Days(CurrentThursday - FirstThursdayOfYear) / 7 + 1
      in
        ISO_Week, 
    Int64.Type
  ), 
  #"ADD relativeMonth" = Table.AddColumn(
    #"ADD Week European", 
    "relativeMonth", 
    each ([Year] - 1)
      * 12 + [Month] - ((Date.Year(DateTime.LocalNow()) - 1) * 12 + Date.Month(DateTime.LocalNow()))
  ), 
  #"ADD relativeDate" = Table.AddColumn(
    #"ADD relativeMonth", 
    "relativeDate", 
    each 
      if [relativeMonth] > 0 then
        "future"
      else if [relativeMonth] = 0 then
        "current month"
      else if [relativeMonth] >= - 1 then
        "last month"
      else if [relativeMonth] >= - 3 then
        "last 1-3 months"
      else if [relativeMonth] >= - 6 then
        "last 4-6 months"
      else if [relativeMonth] >= - 9 then
        "last 7-9 months"
      else if [relativeMonth] >= - 12 then
        "last 10-12 months"
      else
        "1 year and more back"
  ), 
  #"REMV relativeMonth" = Table.RemoveColumns(#"ADD relativeDate", {"relativeMonth"})
in
  #"REMV relativeMonth"