PQ: Letzter Freitag im Quartal

Den letzten Freitag eines Quartals berechnen, wenn nur Quartal und Jahr angegeben wird (z.B. Q3/18)


Video auf YouTubeHinweis:
Die Arbeitsschritte dieses Beitrages sind in einem kleinen Video dokumentiert. Das unterstützende Begleit-Video finden Sie auf YouTube an dieser Stelle.

Beachten Sie bitte: Dieses Begleit-Video enthält zusätzliche Informationen, die im folgenden Beitrag nicht dokumentiert sind! Stellenweise ist die Vorgehensweise eine ganz andere als hier geschildert.


  Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung   

Zugegeben, solch eine Aufgabe ist nicht unbedingt das Paradebeispiel für den Einsatz von Power Query. Aber im Gegensatz zur Vielfalt mehr oder weniger verständlicher Formeln (die keineswegs alle das korrekte Ergebnis bringen) oder der Verwendung von VBA kann PQ auch in solchen Fällen gute Dienste leisten. Laden Sie erst einmal diese Mini-Datei von unserem Server herunter, um sich einen Überblick über die unterschiedlichen Varianten zu verschaffen:

Die Rohdaten …

Die Rohdaten …

Sie erkennen, dass die einzelnen Angaben nicht gerade einem Standard-Datum entsprechen. 😕 – Das Q steht naturgemäß für „Quartal“, die folgende Ziffer für das (numerische) Quartal und nach dem Schrägstrich steht das 2-bzw. 4-stellige Jahr. Das Ergebnis der Berechnung durch Power Query soll jenes Datum sein, welches dem letzten Freitag des entsprechenden Quartals entspricht. Importieren Sie die Liste in PQF2 und benennen Sie die Spalte (Überschrift) zu Quartal um.

Nach dem Import der Tabelle und der „Umbenamsung“ werden sie als erstes die Spalte Duplizieren. Das „Doppelte Lottchen“ 😉 hat die Bewandtnis, dass diese Spalte im endgültigen Ergebnis für Vergleichs- bzw. Kontrollzwecke zur Verfügung steht. Das Duplikat erstellen Sie beispielsweise durch einen Rechtsklick in die Überschrift und dem entsprechenden Punkt im Kontextmenü. Brauchen Sie im Endergebnis nur das berechnete Datum, können bzw. sollten Sie diesen Schritt natürlich überspringen. Normalerweise werden sie jetzt die weiteren Aktionen nur in dieser duplizierten Spalte vornehmen.

Im nächsten Schritt teilen Sie die Spalte Nach Trennzeichen. Da sie im ersten Schritt (und natürlich auch im Endeffekt) das Quartal und die Jahreszahl getrennt haben wollen, bietet sich die von Power Query angebotene Lösung an: der Schrägstrich /.

In der Spalte Quartal – Kopie.1 geht es nun darum, das Q zu entfernen, damit nur die Zahl, der numerische Wert des Quartals übrig bleibt. Sie könnten zwar diese Spalte noch einmal nach dem 1. Zeichen teilen und dann die Spalte mit dem „Q“ löschen, ich empfehle Ihnen aber einen Rechtsklick in die Überschrift und dann den Punkt Werte ersetzen… zu wählen. Hier geben Sie als Zu suchenden Wert das Q ein und das Feld Ersetzen durch lassen Sie leer. Nach einem Klick auf OK ist das Ziel erreicht.

Achten Sie darauf, dass die beiden Spalten mit dem Quartal und der Jahreszahl vom Datentyp: Text sind; das erkennen sie auch daran, dass die Zahlen links ausgerichtet sind. Und in der ersten Daten-Zeile soll 02 (null zwei) stehen, nicht nur die 2. Vermutlich ist aber letzteres der Fall, leider …  😕 Selbst wenn Sie die Spalte noch einmal explizit als Text formatieren, kann es bei der „2″ bleiben. In dem Fall schauen Sie sich einmal das rechte Seitenfenster an:

Hier liegt die Ursache des Problems

Hier liegt die Ursache des Problems

Die in der Abbildung rot markierte Zeile wurde durch Power Query automatisch, ohne Ihr Zutun eingefügt. Löschen Sie diesen Eintrag und falls Sie nach meiner „Aufforderung“ auch den letzten hier gezeigten Schritt, also die Zuweisung des Datentyps Text durchgeführt haben, entfernen Sie auch diese Zeile (Geänderter Typ2) ; sie ist überflüssig geworden. Nun steht in der letzten Spalte der Abfrage wie gewünscht 02.

Wechseln Sie nun zum Register Spalte hinzufügen | Benutzerdefinierte Spalte und geben Sie im Dialog als Neuer Spaltenname den Text Qrt. (als Abkürzung für Quartal) ein. Im großen Feld Benutzerdefinierte Spaltenformel tragen Sie anschließend diese Formel nach dem vorgegebenen Gleichheitszeichen ein:
="1/"&Text.From(Number.From([#"Quartal - Kopie.1"]) * 3) & "/" & [#"Quartal - Kopie.2"]

Die dort aufgeführten [Spaltennamen] übernehmen Sie am besten durch einen Doppelklick im rechten Kasten (Verfügbare Spalten), sofern Sie die Formel per Hand eingeben und nicht aus diesem Script kopiert und in Ihre Abfrage eingefügt haben. Der Erfolg stellt sich nun so dar:

Der berechnete Quartalsbeginn als Text

Der berechnete Quartalsbeginn als Text

Offensichtlich ist es ein Datum, wo statt des Punktes ein Schrägstrich als Trenner zwischen Tag und Monat sowie Monat und Jahr verwendet wird. Und an der Ausrichtung links erkennen Sie auch, dass es sich nicht um ein „echtes“ Datum sondern um einen Text handelt. Power Query hat den Datentyp der Spalte als Beliebig festgelegt, was sie einerseits an dem Symbol in der Überschrift links erkennen aber auch deutlich sehen, wenn Sie zum Register Start wechseln und dort im Menüband beim Datentyp: nachschauen.

Im Register Transformieren Gruppe Datums- & Uhrzeitspalte gibt es das sehr nützliche Symbol Datum. Die Spalte Qrt ist markiert und nach dem erweitern des Menüpunkts Datum klicken Sie auf den einzig aktivierten (nicht ausgegrauten) Menüpunkt: Analysieren. Ruckzuck wird jetzt jeder Zeile dieser Spalte in ein korrektes Datum umgewandelt. Es ist jeweils der 1. Tag des entsprechenden Quartals.  😎 

Erweitern Sie jetzt noch einmal im Menüband den Punkt Datum bei markierter Spalte Qrt. und Sie erkennen, dass dieses Mal fast alle Punkte des DropDowns auswählbar sind. Quartal | Quartalsende und es wird das Monatsanfangs-Datum in der aktuellen Spalte durch den letzten Tag des Quartals ersetzt. Sinnvollerweise werden Sie die Überschrift den neuen Gegebenheiten anpassen und beispielsweise zu Quartalsende umbenennen. Hinweis: Sie hätten für diesen Schritt auch vorher in das Register Spalte hinzufügen wechseln können, dann hätte Power Query eine weitere Spalte mit dem eben von mir vorgeschlagenen Namen automatisch (zusätzlich) generiert.

Nun ist es ja aber nicht unbedingt gegeben, dass dieser letzte Tag des Quartals auch ein Freitag ist. Power Query soll nun erkennen, welcher Wochentag in der Spalte Quartalsende steht und erforderlichenfalls berechnen, wie viele Tage zurück gerechnet werden müssen. Dazu erstellen Sie wiederum eine (Benutzerdefinierte) Hilfsspalte, in welcher sie den numerischen Wochentag des Quartalsendes festlegen; allerdings ist für diese Berechnung nicht der Sonntag (US-Standard) oder Montag (EU) der 1. Tag der Woche, sondern der Freitag. Als neuen Spaltennamen verwende ich WT und füge dann diese Benutzerdefinierte Spaltenformel ein:
Date.DayOfWeek([Quartalsende], Day.Friday)
was folgendes Ergebnis zeitigt, wenn Sie der Power Query-Regel folgen, dass alle Formeln in exakt der vorgegebenen Groß- / Kleinschreibung eingegeben werden müssen:

Die berechneten Differenz-Tage, siehe letzte Spalte "WT"

Die berechneten Differenz-Tage, siehe letzte Spalte „WT“

Sie wissen nun, welche Anzahl von Tagen vom Quartalsende abgezogen werden müssen, um den letzten Freitag des Quartals zu berechnen. Bleibt die Frage, wie Sie das mit PQ umsetzen können. Es wird Sie nicht verwundern, dass auch hier eine neue, Benutzerdefinierte Spalte der Weg zum Ziel ist. Und „natürlich“ ist auch da eine Formel angesagt. Die Überschrift kann  Letzter Freitag / Qrt. sein und als Formel schreiben Sie:
= Date.AddDays([Quartalsende], -[WT])
(bitte Groß- Kleinschreibung beachten) und endlich ist ist nun das ersehnte Datum in einer eigenen Spalte gegeben. Diese Spalte noch als Datum zu formatieren, kann durchaus eine gute Idee sein. 😎 

Als letzten Schritt markieren Sie die Spalten Quartal und Letzter Freitag / Qrt. und nach einem Rechtsklick in eine der beiden Überschriften werden sie Andere Spalten entfernen. Bleibt noch Schließen & laden oder Schließen & laden in… um das Ergebnis entweder in einem neuen Tabellenblatt oder an einer anzugebenden Wunschposition als Formatierte Tabelle zu speichern.

Zugegeben, ich habe diese Lösung mit „Tripelschritten“ erstellt. Den einen oder anderen Schritt könnte ich in einer einzigen Formel mit mehreren Funktionen zusammenfassen. Und ein großer Teil der vomir vorgenommenen Umbenennung der Überschriften ist in erster Linie nur für Einsteiger gedacht. Da dieses alles aber nicht unbedingt einen spürbaren Zeitgewinn im späteren Ablauf bringt, finde ich solch eine Vorgehensweise besser. Sie ist transparenter, einfacher nachvollziehbar und natürlich auch bei Bedarf leichter anzupassen.

Wenn Sie es möchten, lässt sich das natürlich auf jeden beliebigen Wochentag transformieren. Entweder sie ändern in der Funktion den Wochentag oder sie legen in einem Arbeitsblatt eine kleine Tabelle an, wo Power Query den gewünschten Wochentag auslesen und entsprechend auswerten kann. Diese Funktionalität soll aber hier nicht diskutiert werden, fragen Sie aber gerne wegen einer kostenpflichtigen Lösung an.

Hinweis: Meine endgültige Lösung aufbauend auf der hier gezeigten Vorgehensweise können Sie hier herunterladen. Eine stark an ein fast schon fertig erstelltes Video angelehnte Version der *.xlsx (mit weniger Formeln) steht Ihnen hier zum Download bereit.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits freuen …

Reference: #2416

Dieser Beitrag wurde unter Datum & Zeit, Ohne Makro/VBA, Power Query abgelegt und mit , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.