Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
In einem Forum wurde die Frage gestellt, wie die Anzahl der Arbeitstage jedes Quartals in einem definierten Zeitraum berechnet werden kann. Natürlich führen (wie in Excel typisch) viele Wege nach Rom, ich stelle Ihnen hier einen möglichen Weg mit Power Query vor.
Laden Sie diese Datei von unserem Server herunter, wo im Blatt Tabelle1 die kalendarischen Daten für den Projekt-Start und ‑Ende vermerkt sind und im Arbeitsblatt Feiertage alle arbeitsfreien Feiertage der 3 betroffenen Jahre aufgeführt sind. Und bei der Gelegenheit auch gleich ein Hinweis: Es existieren zwar 2 Abfragen in dem File, aber Sie werden rasch erkennen, dass dort ein Fehler enthalten ist:
Fehlermeldung wegen falscher Datenquelle
Power Query sucht für den Import die benannte Tabelle mit dem Namen Tabelle1 bzw. auch nach Tabelle2, welche aber nicht (mehr) existieren. Ich haben die beiden „Intelligenten” Tabellen nach dem Erstellen des Projekts umbenannt und (natürlich) die neuen, „sprechenden” Tabellennamen hier im Beitrag verwendet. Sie können das „bereinigen”, wenn Sie in der Eingabezeile den Teil [Name=„Tabelle1”] dahingehend per Hand ändern: [Name=„Start_Ende”] bzw. [Name=„Feiertage”].
Da alle Daten bereits als formatierte Tabelle vorliegen, können Sie mit Ihrem Vorhaben gleich starten. Importieren Sie in den Power Query-Editor die Tabelle mit den Daten für Start und Ende, speichern Sie diese sofort über Schließen & laden in… und wählen die Möglichkeit Nur Verbindung erstellen. Anschließend werden Sie dieses Vorgehen mit den Daten des Arbeitsblatt Feiertage wiederholen.
Öffnen Sie nun wiederum die Abfrage Start_Ende und im ersten Schritt werden Sie die beiden einzigen Spalten in den Datentyp Datum umwandeln. Ich gebe zu, dass dieser Schritt nicht unbedingt notwendig ist, aber mich stört einfach die wirklich überflüssige Uhrzeit-Angabe 0:00 Uhr. Wechseln Sie nun zu Abfrage Feiertage und reduzieren Sie auch diese Daten auf das reine Datum.
Liste aller Tage von Start bis Ende
Im folgenden Schritt werden Sie eine Liste aller Tage vom ersten bis zum letzten Tag des Projekts erstellen. Dazu wechseln Sie in die Abfrage Start_Ende und aktivieren das Register Spalte hinzufügen. Ein Klick auf Benutzerdefinierte Spalte und es öffnet sich ein Dialog. Tragen Sie hier bei Neuer Spaltenname den Wert Datum ein und schreiben Sie in Benutzerdefinierte Spaltenformel:
Diese Formel ist zielführend, um eine Liste mit allen kalendarischen Daten zu erstellen
Hier noch einmal als Text und auch zum Kopieren (ohne das führende Gleichheitszeichen) die eigentliche Formel:
= List.Dates([Start], Duration.Days([Ende]-[Start])+1,
#duration(1,0,0,0))
Sie achten darauf, exakt die hier dargestellte Groß-Kleinschreibung zu verwenden. Power Query hat in der Abfrage eine neue Spalte erstellt, Überschrift Datum und in der einzigen Zeile das Wort List. Erweitern Sie nun diese Spalte durch einen Klick auf den Doppelpfeil 
und wählen Sie anschließend Auf neue Zeilen ausweiten. Wenn Sie so misstrauisch sind wie ich 😉 werden sie den ersten Eintrag in der Spalte Datum mit dem Wert in der Spalte Start vergleichen, ganz bis zum Ende scrollen und dort feststellen, dass auch hier der Wert in Spalte Ende mit dem in der Spalte Datum übereinstimmt.
Wochenenden entfernen
Da PQ ein umfangreiches Portfolio an Datumsfunktionen hat gibt es natürlich auch diverse Möglichkeiten, die Wochenenden aus dem Daten-Bestand zu entfernen. Ich ziehe den etwas längeren, aus meiner Sicht aber transparenteren Weg über den ausgeschriebenen Namen des Wochentages vor. Die Spalte Datum ist markiert. Spalte hinzufügen | Datum | Tag | Name des Tags. Ruckzuck wird der Name des Wochentags in die neu erstellte Spalte eingetragen. Und jetzt ist es ein leichtes die Spalte so zu filtern, dass Samstage und Sonntage aus der Abfrage entfernt werden. Aus vorher 618 Zeilen sind nun 440 Zeilen geworden. 💡
Auch die Feiertage löschen
Da naturgemäß auch die Feiertage arbeitsfrei sind müssen auch diese aus der Abfrage entfernt werden. Und nun kommt die Abfrage Feiertage auch ins Spiel. Wechseln Sie zum Register Home und Klicken Sie auf Kombinieren. Abfragen zusammenführen und im Dialog wählen Sie im schmalen, mittleren Fenster die Abfrage Feiertage. Markieren Sie im oberen Kasten die Spalte Datum und im unteren die einzige Spalte Feiertage. Bei Join-Art wählen Sie Linker Anti-Join, um jene Zeilen zu filtern, die nur in der Abfrage Start_Ende enthalten sind. Ganz unten im Dialog mit Ihnen noch angezeigt, dass diese Auswahl 18 von 440 Zeilen der ersten Tabelle entfernen wird:
So werden die Feiertage aus der Abfrage entfernt
Die Zahl von 18 Datensätzen ergibt sich aus der Tatasche, dass in de Abfrage Feiertage „nur” 18 Treffer gefunden wurden. Ostersonntag, Pfingstsonntag, etc. sind ja schon im vorherigen Schritt gelöscht worden und außerdem sind ja auch die Feiertage der kompletten Jahre aufgelistet, im ersten und letzten Jahr wurde aber nur ein Teil des Jahres abgeglichen.
Quartal und Jahr zuordnen
Nun ist es an der Zeit, die Tabelle etwas aufzuräumen. Markieren Sie die Spalte Datum, Rechtsklick in die Überschrift und im Kontextmenü Andere Spalten entfernen. Es bleibt nur noch die Spalte mit den kalendarischen Daten aller Arbeitstage erhalten. Das Ziel ist nun, in einer weiteren Spalte Quartal und Jahr auszugeben. In Zeile 1 wäre dieses beispielsweise 4/2019 oder Quartal 4/2019. Auch bei diesem Vorgang sind einige kleine Zwischenschritte sinnvoll. Wechseln Sie zum Register Spalte hinzufügen | Datum | Quartal | Quartal des Jahres. Sie erkennen, dass hier das Quartal innerhalb des entsprechenden Jahres berechnet, jedoch nicht das Jahr als Wert ausgegeben wird. Darum noch einmal die Spalte Datum markieren, Datum | Jahr | Jahr.
Wenn sie nur Quartal und Jahr durch einen / verknüpfen wollen, dann beschreiten Sie diesen relativ einfachen Weg: Markieren Sie erst die Überschrift Quartal, Shift und Klicken Sie dann in die Überschrift Jahr. Wechseln Sie zum Register Transformieren und in der Gruppe/dem Symbol Textspalte wählen Sie Spalten zusammenführen. Bei Trennzeichen markieren Sie Benutzerdefiniert und tragen in das Textfeld den / ein. Als Neuer Spaltenname verwenden Sie beispielsweise Quartal/Jahr. Nach einem OK ist der Erfolg sofort gegeben.
Möchten Sie den Zusatz „Qrt.” oder „Quartal” vor der Quartalsangabe stehen haben, dann können Sie an dieser Stelle entweder eine neue Spalte einfügen, wiederum Textspalte wählen und dort Format | Präfix hinzufügen wählen. Alternativ (ohne den eben beschriebenen Umweg) würde das beispielsweise so gehen: Nachdem sie alle Spalten mit Ausnahme der Spalte Datum entfernt haben, bei markierter Spalte Datum Spalte hinzufügen | Benutzerdefinierte Spalte und geben als neuen Spaltennamen beispielsweise Quartal/Jahr ein. Die Benutzerdefinierte Spaltenformel ist aber schon etwas komplexer:
= "Qrt. " & Text.From(Date.QuarterOfYear([Datum]))
& "/" & Date.ToText([Datum], "yyyy")
… und als Überschrift geben Sie beispielsweise Quartal ein. Sie haben ohne Umweg und mit einem Schlag die entsprechende Spalte erstellt. Diese Vorgehensweise hat einen weiteren Vorteil: Möchten Sie das Jahr nur zweistellig ausgegeben, dann verwenden Sie in der Formel statt der Zeichenfolge "yyyy" logischerweise nur "yy" und der Erfolg ist gegeben.
Arbeitstage je Quartal berechnen
Auf in die letzte Runde. 😀 Das gewünschte Ziel ist ja, die Anzahl der Arbeitstage je Quartal zu berechnen und in einer getrennten Liste/Tabelle auszugeben. Markieren Sie die Spalte Quartal per Rechtsklick in die Überschrift und wählen Sie Gruppieren nach… Im Dialog brauchen Sie keine der Vorgaben zu ändern, es reicht ein Klick auf OK. Das Ergebnis überzeugt, die exakt berechneten Arbeitstage werden in einer Abfrage dargestellt. Schließen & laden oder Schließen & laden in… und sie haben das Ergebnis als Excel-Tabelle vorliegen:
Das gewünschte Ergebnis
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,00€ bis 5,00 € freuen …