killerDateTableRu.pq

## killerDateTableRu.pq
let
    Source = (StartDateParam as date, EndDateParam as date, CultureParameter as any, RuHolidaysSwitchParameter as logical) => let

// killerDateTableRu v1.5
// функция killerDateTableRu создает таблицу с датами. Дополняет ее различными полезными для Time Intelligence столбцами.
// В случае, если четвертый аргумент принимает Дни 1, то функция добавляет столбец с выходными.
// Оригинальная идея philbritton https://gist.github.com/philbritton/9677152
// Дополнил русскими выходными и дополнительными столбцам Максим Уваров
// Причесал код, исправил ошибки Дмитрий Власов https://gist.github.com/DmitriyVlasov/d6e30c476bca35de148260e1ebc95c82
// пример: killerDateTableRu(#date(2017, 2, 1), #date(2017, 2, 4), "RU-ru", 1)


  StartDateParameter = StartDateParam ,
  EndDateParameter = EndDateParam,
  IsRuHolidaysParameter  = RuHolidaysSwitchParameter,

    MONTHS_ORDERED_BY_DEFAULT = {
        "Январь", "Февраль", "Март",
        "Апрель", "Май", "Июнь",
        "Июль", "Август", "Сентябрь",
        "Октябрь", "Ноябрь", "Декабрь"
      },

    getProductionCalendar = ( URL as text ) as nullable table =>
      let
          GetDataFromGovRu =
            Csv.Document(
              Web.Contents( URL ),
              [ Delimiter  = ",",
                Encoding   = 65001,
                QuoteStyle = QuoteStyle.None ]
            ),

          #"Повышенные заголовки" =
            Table.PromoteHeaders( GetDataFromGovRu ),

          #"Переименовали поле в год" =
            Table.RenameColumns( #"Повышенные заголовки",
              {
                {"Год/Месяц", "Год"}
              }
            ),

          #"Выбрали год и месяц" =
            Table.SelectColumns( #"Переименовали поле в год",
              { "Год" } & MONTHS_ORDERED_BY_DEFAULT
            ),

          #"Спрямили календарь" =
            Table.UnpivotOtherColumns( #"Выбрали год и месяц",
              { "Год" }, "Месяц", "День"
            ),

          #"Добавили номер месяца" =
            Table.AddColumn( #"Спрямили календарь", "НомерМесяца", each
              1 + List.PositionOf( MONTHS_ORDERED_BY_DEFAULT, [Месяц] ),
              type number
            ),

          #"Преобразовали строку дат в список" =
            Table.TransformColumns( #"Добавили номер месяца",
              {
                { "День", each Text.Split( _, "," ) }
              }
            ),

          #"Развернули список дней" =
            Table.ExpandListColumn( #"Преобразовали строку дат в список",
              "День"
            ),

          #"Добавили сокращенный день" =
            Table.AddColumn( #"Развернули список дней", "Сокращенный День", each
              Text.Contains( [День], "*" )
            ),

          #"Добавили выходной день" =
            Table.AddColumn( #"Добавили сокращенный день", "Выходной День", each
              not [Сокращенный День]
            ),

          #"Удалили признак сокращенного дня" =
            Table.ReplaceValue( #"Добавили выходной день",
              "*", "", Replacer.ReplaceText, { "День" }
            ),

          #"Уточнли тип полей календаря" =
            Table.TransformColumnTypes( #"Удалили признак сокращенного дня",
              {
                { "Год", Int64.Type },
                { "День", Int64.Type },
                { "НомерМесяца", Int64.Type }
              }
            ),

          #"Рассчитали дату" =
            Table.AddColumn( #"Уточнли тип полей календаря", "Дата", each
              #date( [Год], [НомерМесяца], [День] )
            )
      in
          #"Рассчитали дату",

// Примечание:
//  источник взят отсюда: http://data.gov.ru/opendata/7708660670-proizvcalendar
//  На момент 24.05.2018 в календаре содержатся описание праздников с 1999 года по 2025 год.
    PROD_СALENDAR_URL =
      "http://data.gov.ru/opendata/7708660670-proizvcalendar/data-20180410T1145-structure-20180410T1145.csv?encoding=UTF-8",

    ProductionCalendar = getProductionCalendar( PROD_СALENDAR_URL ),

    ListOfShortday =
      Table.SelectRows( ProductionCalendar, each [Сокращенный День] = true )[Дата],

    ListOfHoliday =
      Table.SelectRows( ProductionCalendar, each [Выходной День] = true )[Дата],

    CheckIfThereIsShortdayList =
      List.Buffer(
        try ListOfShortday otherwise {}
      ),

    CheckIfThereIsHolidayList =
      List.Buffer(
        try ListOfHoliday otherwise {}
      ),

    DayCount =
      Duration.Days(
        Duration.From(
          EndDateParameter - StartDateParameter
        )
      ) + 1,

    MainList =
      List.Dates( StartDateParameter, DayCount, #duration( 1, 0, 0, 0 ) ),

    TableFromList =
      Table.FromList( MainList,
        Splitter.SplitByNothing(), { "Date" }
      ),

    ChangedType =
      Table.TransformColumnTypes( TableFromList,
        {
          {"Date", type date }
        }
      ),

    InsertYear =
      Table.AddColumn( ChangedType, "Year", each
        Date.Year( [Date] ),
        Int64.Type
      ),

    InsertQuarter =
      Table.AddColumn( InsertYear, "QuarterOfYear", each
        Date.QuarterOfYear( [Date] ),
        Int64.Type
      ),

    InsertMonth =
      Table.AddColumn( InsertQuarter, "MonthOfYear", each
        Date.Month( [Date] ),
        Int64.Type
      ),

    InsertDay =
      Table.AddColumn( InsertMonth, "MonthDayNumber", each
        Date.Day( [Date] ),
        Int64.Type
      ),

    InsertDayInt =
      Table.AddColumn( InsertDay, "DateInt", each
        [Year] * 10000 + [MonthOfYear] * 100 + [MonthDayNumber],
        Int64.Type
      ),

    InsertYearMonthNumber =
      Table.AddColumn( InsertDayInt, "YearMonthNumber", each
        [Year] * 100 + [MonthOfYear] * 1,
        Int64.Type
      ),

    InsertMonthName =
      Table.AddColumn( InsertYearMonthNumber, "MonthName", each
        Date.ToText( [Date], "MMMM", CultureParameter ),
        type text
      ),

    InsertCalendarMonth =
      Table.AddColumn( InsertMonthName, "MonthInCalendar", each
        ( try ( Text.Range( [MonthName], 0, 3 ) ) otherwise [MonthName] ) & " " & Number.ToText( [Year] ),
        type text
      ),

    InsertCalendarQtr =
      Table.AddColumn( InsertCalendarMonth, "QuarterInCalendar", each
        "Q" & Number.ToText( [QuarterOfYear] ) & " " & Number.ToText( [Year] ),
        type text
      ),

    InsertDayWeek =
      Table.AddColumn(InsertCalendarQtr, "DayInWeek", each
        Date.DayOfWeek( [Date], Day.Monday ) + 1,
        Int64.Type
      ),

    InsertDayName =
      Table.AddColumn(InsertDayWeek, "DayOfWeekName", each
        Date.ToText( [Date], "dddd", CultureParameter ),
        type text
      ),

    InsertWeekEnding =
      Table.AddColumn(InsertDayName, "WeekEnding", each
        Date.EndOfWeek( [Date], Day.Monday ),
        type date
      ),

    InsertedStartofWeek =
      Table.AddColumn(InsertWeekEnding, "StartOfWeek", each
        Date.StartOfWeek( [Date], Day.Monday ),
        type date
      ),

    InsertedStartofMonth =
      Table.AddColumn(InsertedStartofWeek, "StartOfMonth", each
        Date.StartOfMonth( [Date] ),
        type date
      ),

    InsertWeekofYear =
      Table.AddColumn(InsertedStartofMonth, "WeekOfYear", each
        Date.WeekOfYear( [Date], Day.Monday ),
        Int64.Type
      ),

    InsertDayofYear =
      Table.AddColumn(InsertWeekofYear, "DayOfYear", each
        Date.DayOfYear( [Date] ),
        Int64.Type
        ),

    listBufferMonths =
      List.Buffer(
        List.Distinct(
          InsertDayofYear[StartOfMonth]
        )
        ),

    AddedNumberOfMonth =
      Table.AddColumn( InsertDayofYear, "SequentialMonthNumber", each
        List.PositionOf( listBufferMonths, [StartOfMonth]) + 1,
        Int64.Type
      ),

    SequentialMonthNumberReverse =
      Table.AddColumn( AddedNumberOfMonth, "SequentialMonthNumberReverse", each
        List.PositionOf(
          List.Reverse( listBufferMonths ),
          [StartOfMonth]
        ) + 1,
        Int64.Type
      ),

    listBufferWeeks =
      List.Buffer(
        List.Distinct( SequentialMonthNumberReverse[StartOfWeek] )
      ),

    InsertSequentialDayNumber =
      Table.AddIndexColumn(
        SequentialMonthNumberReverse,
        "SequentialDayNumber",
        1,
        1
      ),

    InsertSequentialDayNumberReverse =
      Table.AddIndexColumn(
        InsertSequentialDayNumber,
        "SequentialDayNumberReverse",
        -Table.RowCount(InsertSequentialDayNumber) + 1,
        1
      ),

    InsertSequentialNumberOfWeek =
      Table.AddColumn(
        InsertSequentialDayNumberReverse,
        "SequentialWeekNumberReverse",
        each Number.IntegerDivide([SequentialDayNumberReverse], 7),
        Int64.Type),

    AddedNumberOfWeeks =
      Table.AddColumn(
        InsertSequentialNumberOfWeek,
        "SequentialCalendarWeekNumber",
        each List.PositionOf( listBufferWeeks, [StartOfWeek] ) + 1,
        Int64.Type
      ),

    AddedNumberOfWeeksReverse = Table.AddColumn(
      AddedNumberOfWeeks,
        "SequentialCalendarWeekNumberReverse",
        each [SequentialCalendarWeekNumber] - List.Count(listBufferWeeks),
        Int64.Type
      ),

    insertRuHolidaysColumn =
      Table.AddColumn(
        AddedNumberOfWeeksReverse,
        "Holiday",
        each
          if List.Count( CheckIfThereIsHolidayList ) = 0
          then null
          else
            if List.Contains( CheckIfThereIsHolidayList, [Date])
            then true
            else false
      ),

    insertRuShortdaysColumn =
      Table.AddColumn( insertRuHolidaysColumn, "Shortday", each
        if List.Count( CheckIfThereIsShortdayList ) = 0
        then null
        else
          if List.Contains( CheckIfThereIsShortdayList, [Date])
          then true
          else false
      ),

    FinalResult = if IsRuHolidaysParameter
  then insertRuShortdaysColumn
  else AddedNumberOfWeeksReverse,
    #"Filtered Rows" = Table.SelectRows(FinalResult, each true),
        #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"SequentialWeekNumberReverse", Order.Descending}})
    in
        #"Sorted Rows"
in
    Source