Kalendertabelle in Power Query erzeugen
Mit dem folgenden Code könnt Ihr eine Kalendertabelle aus eueren Daten in Excel oder Power BI erzeugen. Ich habe hier in diesem Fall eine Datei mit einer Tabelle namens factSales. Diese hat eine Spalte namens Datum.
Der Clou an dieser Formel ist, dass nicht nur der Kalender dynamisch erzeugt wird, sondern auch Kalenderwoche, Quartal und Monat zusammen für die Auswertung erzeugt werden.
Ich verlasse mich nicht auf die Kalendertabelle, die man automatisch erzeugen kann, da hier ich einfach mehr Kontrolle über die Daten habe.
In einem späteren Video werde ich den Code erläutern und dann noch per API-Abfrage um die Feiertage eines Bundeslandes erzeugen.
Diesen Code nun einfach Kopieren und in den Power Query-Editor einer leeren Abfrage einfügen.
Code startet hier:
let Quelle = Excel.CurrentWorkbook(){[Name="factSales"]}[Content], DatumZuDate = Table.TransformColumnTypes(Quelle,{"Datum", type date}), // greift auf Tabelle factSales zu. Dort ist nur die Spalte Datum interessant und wird als Datum formatiert BerechneterJahresbeginn = Table.TransformColumns(DatumZuDate,{{"Datum", Date.StartOfYear, type date}}), JahresbeginnZuZahl = Table.TransformColumnTypes(BerechneterJahresbeginn,{{"Datum", Int64.Type}}), DatumStart = List.Min(JahresbeginnZuZahl[Datum]), /* Jedes Datum wird auf Jahresanfang gesetzt. Somit erhält man das kleineste Datum des frühesten Jahres Es resultiert dann ein Wert, der als Listenwert abgerufen werden kann*/ BerechnetesJahresende = Table.TransformColumns(DatumZuDate,{{"Datum", Date.EndOfYear, type date}}), JahresendeZuZahl = Table.TransformColumnTypes(BerechnetesJahresende,{{"Datum", Int64.Type}}), DatumEnde = List.Max(JahresendeZuZahl[Datum]), /* Jedes Datum wird auf Jahresende gesetzt. Somit erhält man das größte Datum des letzten Jahres Es resultiert dann ebenfallsein Wert, der als Listenwert abgerufen werden kann*/ Liste = {DatumStart .. DatumEnde}, ListeInTabelleKonvertiert = Table.FromList(Liste, Splitter.SplitByNothing(), null, null, ExtraValues.Error), // Aus DatumStart und Datumende wird eine Zahlenliste erzeugt, die dann in eine Tabelle gewandelt wird ZahlenZuDatum = Table.TransformColumnTypes(ListeInTabelleKonvertiert,{{"Column1", type date}}), DatumSpalte = Table.RenameColumns(ZahlenZuDatum,{{"Column1", "Datum"}}), // Die Zahlen werden in ein Datum verwandelt, somit erhält man komplette Tage aller notwendigen Jahre JahrEingefügt = Table.AddColumn(DatumSpalte, "Jahr", each Date.Year([Datum]), Int64.Type), MonatEingefügt = Table.AddColumn(JahrEingefügt, "Monat", each Date.Month([Datum]), Int64.Type), QuartalEingefügt = Table.AddColumn(MonatEingefügt, "Quartal", each Date.QuarterOfYear([Datum]), Int64.Type), // Jahr, Monat und Quartal werden als Zahlen eingefügt JahrQuartal = Table.AddColumn(QuartalEingefügt, "Jahr Quartal", each Text.Combine({Text.From([Jahr], "de-DE"), Text.From([Quartal], "de-DE")}, "-"), type text), // Jahr und Quartal werden für spätere Auswertungen vereint MonatZweistellig = Table.AddColumn(JahrQuartal, "Jahr Monat", each if [Monat] < 10 then Text.Combine( {Text.From([Jahr], "de-DE"), "-0", Text.From([Monat], "de-DE")} ) else Text.Combine( {Text.From([Jahr], "de-DE"), "-", Text.From([Monat], "de-DE")} ) ), /*Jahr, Bindestrich und zweistellige Monatsnummer werden als neue Spalte ergänzt. Die WENN-Funktion prüft, ob das Jahr einstellig ist */ Kalenderwoche = Table.AddColumn(MonatZweistellig, "KW", each if Number.RoundDown((Date.DayOfYear([Datum])-(Date.DayOfWeek([Datum], Day.Monday))+9)/7)=0 then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Datum])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Datum])-1,12,31), Day.Monday))+9)/7) else if (Number.RoundDown((Date.DayOfYear([Datum])-(Date.DayOfWeek([Datum], Day.Monday))+9)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Datum]),12,31), Day.Monday)<3)) then 1 else Number.RoundDown((Date.DayOfYear([Datum])-(Date.DayOfWeek([Datum], Day.Monday))+9)/7)), // Die KW nach ISO 8601 wird ermittelt JahrKWCalc = Table.AddColumn(Kalenderwoche , "JahrKW", each if [Monat]=1 and [KW]>51 then [Jahr]-1 else if [Monat]=12 and [KW] = 1 then [Jahr]+1 else [Jahr]), // Das Jahr der entsprechenden KW wird ermittelt. Dies weicht eventuell vom Kalenderjahr ab. ZahlenZuText = Table.TransformColumnTypes(JahrKWCalc ,{{"JahrKW", type text}, {"KW", type text}}), JahrKWZusammen = Table.AddColumn(ZahlenZuText , "Jahr KW", each [JahrKW]&"-"& Text.End("0"&[KW], 2), type text), // Jahr und KW werden vereint. Hier mal nicht die TextCombine-Formel von oben SpaltenRaus = Table.RemoveColumns(JahrKWZusammen ,{"KW", "JahrKW"}), // Überflüssige Spalten raus und noch Wochentag = Table.AddColumn(SpaltenRaus, "Tag", each Date.DayOfWeekName([Datum]), type text), WochentagZahl = Table.AddColumn(Wochentag, "Tag der Woche", each Date.DayOfWeek([Datum], 1) + 1, Int64.Type), Monatsname = Table.AddColumn(WochentagZahl, "Monatsname", each Date.MonthName([Datum]), type text) // Noch ein paar Informationen ergänzt in Monatsname
Code endet hier!
Hier ist die Datei zum Download mit ein paar Demodaten:
E1031 Verkäufe Kalendertabelle
Freue mich auf Feedback und Danke für die Unterstützung des Kanals!
Sonnige Grüße
Andreas
Du muss angemeldet sein, um einen Kommentar zu veröffentlichen.