Berechnung von Nettoarbeitstagen in Power Query
Schritt-für-Schritt-Anleitung
Um die Nettoarbeitstage mit Power Query zu berechnen, befolge diese Schritte:
-
Datenquelle erstellen: Lege eine Excel-Tabelle an, die deine Feiertage und Daten enthält. Benenne die Tabellen sinnvoll, z.B. "Feiertage" für die Feiertage und "Eingang" für die Datensätze, an denen du die Nettoarbeitstage berechnen möchtest.
-
Power Query öffnen: Gehe in Excel zu Daten > Abfragen und Verbindungen und wähle Abfragen bearbeiten.
-
Feiertage importieren: Verwende den folgenden Code, um die Feiertage aus deiner Tabelle zu importieren:
let
Quelle = Excel.CurrentWorkbook(){[Name="Feiertage"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Feiertage", type date}}),
Feiertage = #"Geänderter Typ"[Feiertage]
in
Feiertage
-
Daten für Nettoarbeitstage vorbereiten: Füge eine benutzerdefinierte Spalte hinzu, um die Liste aller Tage zwischen zwei Datumswerten zu erstellen:
= List.Dates([Datum von], Number.From([Datum bis] - [Datum von]) + 1, #duration(1,0,0,0))
-
Wochenendtage herausfiltern: Erstelle eine weitere benutzerdefinierte Spalte, um die Tage ohne Wochenenden zu ermitteln:
= List.Select([Liste aller Tage als Datum], each Date.DayOfWeek(_) < 5)
-
Nettoarbeitstage berechnen: Füge eine letzte benutzerdefinierte Spalte hinzu, die die Nettoarbeitstage berechnet:
= List.Count([Liste ohne WE]) - List.Count(List.Intersect({[Liste ohne WE], Feiertage}))
Häufige Fehler und Lösungen
-
Formatierungsfehler: Achte darauf, dass die Spalten "Datum von" und "Datum bis" als Datumswerte formatiert sind. Bei Fehlern könnte es daran liegen, dass die Datentypen nicht übereinstimmen.
-
Liste nicht gefunden: Wenn der Fehler "Liste nicht gefunden" auftritt, überprüfe, ob die Tabelle der Feiertage korrekt benannt und in Power Query importiert wurde.
-
Falsche Datumswerte: Stelle sicher, dass du die Datumswerte in der richtigen Reihenfolge eingibst, da einen vertauschten "von"- und "bis"-Wert zu unerwarteten Ergebnissen führen kann.
Alternative Methoden
Wenn du die Nettoarbeitstage auch in Power BI berechnen möchtest, kannst du ähnliche Schritte befolgen. Die DAX-Funktion WORKDAY kann auch verwendet werden, um Nettoarbeitstage unter Berücksichtigung von Feiertagen zu berechnen.
Ein Beispiel in DAX:
Nettoarbeitstage =
COUNTROWS(
FILTER(
CALENDAR([Datum von], [Datum bis]),
NOT(WEEKDAY([Date], 2) IN {6, 7}) &&
NOT([Date] IN VALUES(Feiertage[Feiertage]))
)
)
Praktische Beispiele
Beispiel 1: Berechnung der Nettoarbeitstage zwischen dem 1. Januar 2022 und dem 31. Januar 2022 unter Berücksichtigung von Feiertagen.
- Erstelle eine Tabelle mit Feiertagen, z.B. Neujahr (1. Januar), Tag der Arbeit (1. Mai).
- Verwende die oben genannten Schritte, um die Nettoarbeitstage für den Januar zu berechnen.
Beispiel 2: Wenn du die Nettoarbeitstage bis heute berechnen möchtest, kannst du die heute Funktion von Power Query verwenden:
= List.Dates([Eingang], Number.From(Date.From(DateTime.LocalNow()) - [Eingang]) + 1, #duration(1,0,0,0))
Tipps für Profis
-
Optimierung der Abfragen: Achte darauf, die Abfragen so schlank wie möglich zu halten, um die Ladezeiten zu optimieren. Vermeide unnötige Zwischenschritte.
-
Feiertage dynamisch: Halte die Feiertagstabelle aktuell, um sicherzustellen, dass die Berechnungen auch in Zukunft korrekt sind.
-
Verwendung von Named Ranges: Überlege, benannte Bereiche in Excel zu verwenden, um die Wartbarkeit deiner Abfragen zu erhöhen.
FAQ: Häufige Fragen
1. Wie kann ich Feiertage in Power Query hinzufügen?
Du kannst eine Tabelle in Excel erstellen, in der die Feiertage aufgeführt sind, und diese dann mit Excel.CurrentWorkbook() in Power Query importieren.
2. Was mache ich, wenn ich Feiertage in mehreren Ländern berücksichtigen möchte?
Erstelle separate Tabellen für jedes Land und kombiniere die Feiertage in Power Query, bevor du die Nettoarbeitstage berechnest.
3. Kann ich Nettoarbeitstage auch für den aktuellen Monat berechnen?
Ja, indem du die heute Funktion verwendest, kannst du Nettoarbeitstage bis zum aktuellen Datum berechnen.