Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.