fnGetCalenderTable.pq

let
fn=(StartJahr, EndJahr, optional Culture, optional StartFiskaljahr)=>
let
    StartFiskJahr = if StartFiskaljahr = null then "Jan" else StartFiskaljahr,
    GetStartDay = #date(StartJahr,1,1),
    GetEndDay = #date(EndJahr,12,31),
    GetCultureDefaultGermany = if Culture = null then "de-de" else Culture, 
    DayCount = Duration.Days(Duration.From(GetEndDay - GetStartDay)) + 1, 
    GetListOfDates = List.Dates(GetStartDay,DayCount,#duration(1,0,0,0)), 
    TableFromList = Table.FromList(GetListOfDates, Splitter.SplitByNothing()),    
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), 
    Date = Table.RenameColumns(ChangedType,{{"Column1", "Datum"}}),
    #"AddFull date Description" = Table.AddColumn(Date, "Volle Datumsbezeichnung", each Date.ToText([Datum], "dd. MMMM yyyy", Culture)),
    AddWeekDaySort = Table.AddColumn(#"AddFull date Description", "Tag der Woche (Sort.)", each Date.DayOfWeek([Datum], Day.Monday) + 1, Int64.Type),
    AddMonthDaySort = Table.AddColumn(AddWeekDaySort, "Tag des Monats (Sort.)", each Date.Day([Datum])),
    #"Geänderter Typ1" = Table.TransformColumnTypes(AddMonthDaySort,{{"Tag des Monats (Sort.)", Int64.Type}}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Geänderter Typ1",{{"Volle Datumsbezeichnung", type text}}),
    AddDayKey = Table.AddIndexColumn(#"Geänderter Typ", "TagesKey", 1, 1),
    #"GDTyp" = Table.TransformColumnTypes(AddDayKey,{{"TagesKey", Int64.Type}}),
    AddDayName = Table.AddColumn(#"GDTyp", "Name des Tags", each Date.DayOfWeekName([Datum], Culture), type text),
    AddDaysNameShort = Table.AddColumn(AddDayName, "Name des Tages_kurz", each Date.ToText([Datum],"ddd", Culture)),
    #"Geänderter Typ2" = Table.TransformColumnTypes(AddDaysNameShort,{{"Name des Tages_kurz", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Geänderter Typ2", "Wochentags_Flag", each if [#"Tag der Woche (Sort.)"] < 6 then "Weekday" else "Weekend"),
    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte2",{{"Wochentags_Flag", type text}}),
    AddWeekOfYear = Table.AddColumn(#"Geänderter Typ3", "Woche des Jahres (Sort.)", each Date.WeekOfYear([Datum]), Int64.Type),
    AddYearWeek = Table.AddColumn(AddWeekOfYear, "JahrWoche", each Date.Year([Datum])*100+[#"Woche des Jahres (Sort.)"]),
    #"Changed Type1" = Table.TransformColumnTypes(AddYearWeek,{{"JahrWoche", Int64.Type}}),
    TableWeekKey = Table.AddIndexColumn(Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(List.Sort(List.Distinct(#"Changed Type1"[JahrWoche]),Order.Ascending), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "YearWeek"}}),{{"YearWeek", Int64.Type}}), "WeekKey", 1, 1),
    AddWeeKey = Table.NestedJoin(#"Changed Type1",{"JahrWoche"},TableWeekKey,{"YearWeek"},"WK",JoinKind.LeftOuter),
    #"Expanded WK" = Table.ExpandTableColumn(AddWeeKey, "WK", {"WeekKey"}, {"WochenKey"}),
    RemovedYearWeek = Table.RemoveColumns(#"Expanded WK",{"JahrWoche"}),
    #"Changed Type" = Table.TransformColumnTypes(RemovedYearWeek,{{"WochenKey", Int64.Type}}),
    AddWeekName = 
        Table.AddColumn(#"Changed Type", "Wochenname", each "KW"&Text.PadStart(Number.ToText([#"Woche des Jahres (Sort.)"]),2,"0") &" "&Number.ToText(Date.Year([Datum]))),
    #"Changed Type14" = Table.TransformColumnTypes(AddWeekName,{{"Wochenname", type text}}),

    fnGetIsoWeekNumber = (MyDate as date) =>
      //Source --> https://blogs.office.com/en-us/2009/06/30/week-numbers-in-excel/
    let
      //MyDate = #date(2016,1,3),
      Part1 = Number.From(MyDate) - Number.From(#date(Date.Year(Date.From(Number.From(MyDate) - (Date.DayOfWeek(Date.AddDays(MyDate,-1), Day.Sunday) + 1) + 4)),1,3)),
      Part2 = Date.DayOfWeek(#date(Date.Year(Date.From(Number.From(MyDate) - (Date.DayOfWeek(Date.AddDays(MyDate,-1), Day.Sunday) +1) + 4)),1,3), Day.Sunday)+1 + 5,
      ISOWeekNumber = Number.IntegerDivide(Part1 + Part2, 7)
    in
      ISOWeekNumber,
    AddIsoWeek = Table.AddColumn(#"Changed Type14", "IsoKalenderwoche", each fnGetIsoWeekNumber([Datum]), type number),
    AddISOWeekName = Table.AddColumn(AddIsoWeek, "Iso_Kalenderwochenname", each "KW"&Text.PadStart(Number.ToText([IsoKalenderwoche]), 2, "0") &" "&Number.ToText(Date.Year([Datum])) &" (ISO)"),
    AddWeekStart = Table.AddColumn(AddISOWeekName, "Wochenbeginn", each Date.StartOfWeek([Datum]), type date),
    AddMonthSort = Table.AddColumn(AddWeekStart, "Monat (Sort.)", each Date.Month([Datum]), Int64.Type),
    AddMonthName = Table.AddColumn(AddMonthSort, "Monatsname", each Date.MonthName([Datum], Culture), type text),
    AddMonthNameShort = Table.AddColumn(AddMonthName, "Monatsname_kurz", each Date.ToText([Datum], "MMM", Culture)),
    #"Geänderter Typ6" = Table.TransformColumnTypes(AddMonthNameShort,{{"Monatsname_kurz", type text}}),
    AddMonthNameShort_Year = Table.TransformColumnTypes(Table.AddColumn(#"Geänderter Typ6", "Monatsname_kurz_Jahr", each [Monatsname_kurz] &" "& Number.ToText(Date.Year([Datum]))),{{"Monatsname_kurz_Jahr", type text}}),
    AddYearMonth = Table.TransformColumnTypes(Table.AddColumn(AddMonthNameShort_Year, "JahrMonat", each Date.Year([Datum])*100 + [#"Monat (Sort.)"]),{{"JahrMonat", Int64.Type}}),
    TableYearMonth = Table.AddIndexColumn(Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(List.Sort(List.Distinct(AddYearMonth[JahrMonat]),Order.Ascending), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "YearMonth"}}),{{"YearMonth", Int64.Type}}), "MonthKey", 1, 1),
    #"Zusammenführte Abfragen" = Table.NestedJoin(AddYearMonth,{"JahrMonat"},TableYearMonth,{"YearMonth"},"MK",JoinKind.LeftOuter),
    #"Erweiterte MK" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "MK", {"MonthKey"}, {"MonthKey"}),
    AddEoM = Table.AddColumn(#"Erweiterte MK", "Monatsende", each Date.EndOfMonth([Datum]), type date),
    AddQuarter = Table.AddColumn(AddEoM, "Quartal (Sort.)", each Date.QuarterOfYear([Datum]), Int64.Type),
    AddQuarterYearName = Table.TransformColumnTypes(Table.AddColumn(AddQuarter, "Quartalsname", each "Q"&Number.ToText([#"Quartal (Sort.)"]) &" "&Number.ToText(Date.Year([Datum]))),{{"Quartalsname", type text}}),
    AddYearQuarter = Table.TransformColumnTypes(Table.AddColumn(AddQuarterYearName, "JahrQuartal", each Date.Year([Datum]) * 100 + [#"Quartal (Sort.)"]),{{"JahrQuartal", Int64.Type}}),
    TableYearQuarter = Table.AddIndexColumn(Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(List.Sort(List.Distinct(#"AddYearQuarter"[JahrQuartal]),Order.Ascending), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "YearQuarter"}}),{{"YearQuarter", Int64.Type}}), "QuartalKey", 1, 1),
    Benutzerdefiniert1 = Table.NestedJoin(AddYearQuarter,{"JahrMonat"},TableYearQuarter,{"YearQuarter"},"QK",JoinKind.LeftOuter),
    AddQuartalKey = Table.ExpandTableColumn(Benutzerdefiniert1, "QK", {"QuartalKey"}, {"QuartalKey"}),
    AddHalfYearSort = Table.TransformColumnTypes(Table.AddColumn(AddQuartalKey, "Halbjahr (Sort.)", each if Date.Month([Datum]) < 7 then 1 else 2),{{"Halbjahr (Sort.)", Int64.Type}}),
    AddHalfYearName = Table.TransformColumnTypes(Table.AddColumn(AddHalfYearSort, "Halbjahresname", each "HJ"&Number.ToText([#"Halbjahr (Sort.)"])&" "&Number.ToText(Date.Year([Datum]))),{{"Halbjahresname", type text}}),
    AddYearHalfYear = Table.TransformColumnTypes(Table.AddColumn(AddHalfYearName, "JahrHalbjahr", each Date.Year([Datum])*100+[#"Halbjahr (Sort.)"]),{{"JahrHalbjahr", Int64.Type}}),
    TableYearHalfYear = Table.AddIndexColumn(Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(List.Sort(List.Distinct(AddYearHalfYear[JahrHalbjahr]),Order.Ascending), Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "YearHalfYear"}}),{{"YearHalfYear", Int64.Type}}), "HalfYearKey", 1, 1),
    Benutzerdefiniert2 = Table.NestedJoin(AddYearHalfYear,{"JahrHalbjahr"},TableYearHalfYear,{"YearHalfYear"},"HYK",JoinKind.LeftOuter),
    AddHalfYearKey = Table.ExpandTableColumn(Benutzerdefiniert2, "HYK", {"HalfYearKey"}, {"HalfYearKey"}),
    AddYear = Table.AddColumn(AddHalfYearKey, "Jahr", each Date.Year([Datum]), Int64.Type),
    IsLeapYear = Table.TransformColumnTypes(Table.AddColumn(AddYear, "Schaltjahr", each Date.IsLeapYear( [Datum] )),{{"Schaltjahr", type logical}}),
    
    //==============================================================================Fiskaljahresberechnungen=================================================================
    GetStartMonatsnummerFiskaljahr = List.PositionOf({"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}, StartFiskJahr ) + 1,
    AddFiscalYear = Table.TransformColumnTypes(Table.AddColumn(IsLeapYear, "FiskalJahr", each if Date.Month([Datum]) < GetStartMonatsnummerFiskaljahr then Date.Year([Datum]) -1 else Date.Year([Datum])),{{"FiskalJahr", Int64.Type}}),
    AddFiscalMonth = Table.TransformColumnTypes(Table.AddColumn(#"AddFiscalYear", "FiskalMonat", each if ( Date.Month([Datum]) >= GetStartMonatsnummerFiskaljahr) then Date.Month([Datum]) - GetStartMonatsnummerFiskaljahr +1 
else 
Date.Month([Datum])+13-GetStartMonatsnummerFiskaljahr),{{"FiskalMonat", Int64.Type}}),
    AddFiscalHalfYear = Table.TransformColumnTypes(Table.AddColumn(AddFiscalMonth, "FiskalHalbjahr", each if [FiskalMonat]<7 then 1 else 2), {{"FiskalHalbjahr", Int64.Type}}),
    AddFiscalQuarter = Table.TransformColumnTypes(Table.AddColumn(AddFiscalHalfYear, "FiskalQuartal", each Number.RoundUp([FiskalMonat]/3 ,0)), {{"FiskalQuartal", Int64.Type}}),
    //==============================================================================Fiskaljahresberechnungen=================================================================
    Output = if StartFiskJahr = "Jan" then #"IsLeapYear"else #"AddFiscalQuarter"
in
    Output,
    fnType = type function(
              StartJahr as number,
              EndJahr as number, 
              optional Culture as (type text meta [Documentation.AllowedValues={"de-de", "en-US"}]), 
              optional StartFiskaljahr as (type text meta[Documentation.AllowedValues={"Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"}])
               ) as list meta [
                                Documentation.Name="fnGetCalendarTable",
                                Documentation.LongDescription="Diese Funktion erstellt eine Kalendertabelle, angelehnt an den Vorschlägen der Kimball-Group.",
                                Documentation.Author="Lars Schreiber, ssbi-blog.de"
                              ]
in
Value.ReplaceType(fn, fnType)