PQ: Letzter Freitag im Quartal

  Wis­sens­stand: Lev­el 1 ⇒ Ein­steiger in PQ, keine/kaum Erfahrung   

Zugegeben, solch eine Auf­gabe ist nicht unbe­d­ingt das Parade­beispiel für den Ein­satz von Pow­er Query. Aber im Gegen­satz zur Vielfalt mehr oder weniger ver­ständlich­er Formeln (die keineswegs alle das kor­rek­te Ergeb­nis brin­gen) oder der Ver­wen­dung von VBA kann PQ auch in solchen Fällen gute Dien­ste leis­ten. Laden Sie erst ein­mal diese Mini-Datei von unserem Serv­er herunter, um sich einen Überblick über die unter­schiedlichen Vari­anten zu ver­schaf­fen:

Die Roh­dat­en …

Sie erken­nen, dass die einzel­nen Angaben nicht ger­ade einem Stan­dard-Datum entsprechen. 😕 – Das Q ste­ht naturgemäß für „Quar­tal”, die fol­gende Zif­fer für das (numerische) Quar­tal und nach dem Schrägstrich ste­ht das 2‑bzw. 4‑stellige Jahr. Das Ergeb­nis der Berech­nung durch Pow­er Query soll jenes Datum sein, welch­es dem let­zten Fre­itag des entsprechen­den Quar­tals entspricht. Importieren Sie die Liste in PQ, F2 und benen­nen Sie die Spalte (Über­schrift) zu Quar­tal um.

Nach dem Import der Tabelle und der „Umbe­nam­sung” wer­den sie als erstes die Spalte Duplizieren. Das „Dop­pelte Lottchen” 😉 hat die Bewandt­nis, dass diese Spalte im endgülti­gen Ergeb­nis für Ver­gle­ichs- bzw. Kon­trol­lzwecke zur Ver­fü­gung ste­ht. Das Dup­likat erstellen Sie beispiel­sweise durch einen Recht­sklick in die Über­schrift und dem entsprechen­den Punkt im Kon­textmenü. Brauchen Sie im Endergeb­nis nur das berech­nete Datum, kön­nen bzw. soll­ten Sie diesen Schritt natür­lich über­sprin­gen. Nor­maler­weise wer­den sie jet­zt die weit­eren Aktio­nen nur in dieser duplizierten Spalte vornehmen.

Im näch­sten Schritt teilen Sie die Spalte Nach Trennze­ichen. Da sie im ersten Schritt (und natür­lich auch im End­ef­fekt) das Quar­tal und die Jahreszahl getren­nt haben wollen, bietet sich die von Pow­er Query ange­botene Lösung an: der Schrägstrich /.

In der Spalte Quar­tal – Kopie.1 geht es nun darum, das Q zu ent­fer­nen, damit nur die Zahl, der numerische Wert des Quar­tals übrig bleibt. Sie kön­nten zwar diese Spalte noch ein­mal nach dem 1. Zeichen teilen und dann die Spalte mit dem „Q” löschen, ich empfehle Ihnen aber einen Recht­sklick in die Über­schrift und dann den Punkt Werte erset­zen… zu wählen. Hier geben Sie als Zu suchen­den Wert das Q ein und das Feld Erset­zen durch lassen Sie leer. Nach einem Klick auf OK ist das Ziel erre­icht.

Acht­en Sie darauf, dass die bei­den Spal­ten mit dem Quar­tal und der Jahreszahl vom Daten­typ: Text sind; das erken­nen sie auch daran, dass die Zahlen links aus­gerichtet sind. Und in der ersten Dat­en-Zeile soll 02 (null zwei) ste­hen, nicht nur die 2. Ver­mut­lich ist aber let­zteres der Fall, lei­der …  😕 Selb­st wenn Sie die Spalte noch ein­mal expliz­it als Text for­matieren, kann es bei der „2” bleiben. In dem Fall schauen Sie sich ein­mal das rechte Seit­en­fen­ster an:

Hier liegt die Ursache des Prob­lems

Die in der Abbil­dung rot markierte Zeile wurde durch Pow­er Query automa­tisch, ohne Ihr Zutun einge­fügt. Löschen Sie diesen Ein­trag und falls Sie nach mein­er „Auf­forderung” auch den let­zten hier gezeigten Schritt, also die Zuweisung des Daten­typs Text durchge­führt haben, ent­fer­nen Sie auch diese Zeile (Geän­dert­er Typ2) ; sie ist über­flüs­sig gewor­den. Nun ste­ht in der let­zten Spalte der Abfrage wie gewün­scht 02.

Wech­seln Sie nun zum Reg­is­ter Spalte hinzufü­gen | Benutzerdefinierte Spalte und geben Sie im Dia­log als Neuer Spal­tenname den Text Qrt. (als Abkürzung für Quar­tal) ein. Im großen Feld Benutzerdefinierte Spal­tenformel tra­gen Sie anschließend diese Formel nach dem vorgegebe­nen Gle­ich­heit­sze­ichen ein:
="1/"&Text.From(Number.From([#"Quartal - Kopie.1"]) * 3) & "/" & [#"Quartal - Kopie.2"]

Die dort aufge­führten [Spal­tenna­men] übernehmen Sie am besten durch einen Dop­pelk­lick im recht­en Kas­ten (Ver­füg­bare Spal­ten), sofern Sie die Formel per Hand eingeben und nicht aus diesem Script kopiert und in Ihre Abfrage einge­fügt haben. Der Erfolg stellt sich nun so dar:

Der berech­nete Quar­tals­be­ginn als Text

Offen­sichtlich ist es ein Datum, wo statt des Punk­tes ein Schrägstrich als Tren­ner zwis­chen Tag und Monat sowie Monat und Jahr ver­wen­det wird. Und an der Aus­rich­tung links erken­nen Sie auch, dass es sich nicht um ein „echt­es” Datum son­dern um einen Text han­delt. Pow­er Query hat den Daten­typ der Spalte als Beliebig fest­gelegt, was sie ein­er­seits an dem Sym­bol in der Über­schrift links erken­nen aber auch deut­lich sehen, wenn Sie zum Reg­is­ter Start wech­seln und dort im Menüband beim Daten­typ: nach­schauen.

Im Reg­is­ter Trans­formieren Gruppe Datums- & Uhrzeitspalte gibt es das sehr nüt­zliche Sym­bol Datum. Die Spalte Qrt ist markiert und nach dem erweit­ern des Menüpunk­ts Datum Klick­en Sie auf den einzig aktivierten (nicht aus­ge­graut­en) Menüpunkt: Analysieren. Ruck­zuck wird jet­zt jed­er Zeile dieser Spalte in ein kor­rek­tes Datum umge­wan­delt. Es ist jew­eils der 1. Tag des entsprechen­den Quar­tals.  😎 

Erweit­ern Sie jet­zt noch ein­mal im Menüband den Punkt Datum bei markiert­er Spalte Qrt. und Sie erken­nen, dass dieses Mal fast alle Punk­te des Drop­Downs auswählbar sind. Quar­tal | Quar­talsende und es wird das Monat­san­fangs-Datum in der aktuellen Spalte durch den let­zten Tag des Quar­tals erset­zt. Sin­nvoller­weise wer­den Sie die Über­schrift den neuen Gegeben­heit­en anpassen und beispiel­sweise zu Quar­talsende umbe­nen­nen. Hin­weis: Sie hät­ten für diesen Schritt auch vorher in das Reg­is­ter Spalte hinzufü­gen wech­seln kön­nen, dann hätte Pow­er Query eine weit­ere Spalte mit dem eben von mir vorgeschla­ge­nen Namen automa­tisch (zusät­zlich) gener­iert.

Nun ist es ja aber nicht unbe­d­ingt gegeben, dass dieser let­zte Tag des Quar­tals auch ein Fre­itag ist. Pow­er Query soll nun erken­nen, welch­er Wochen­tag in der Spalte Quar­talsende ste­ht und erforder­lichen­falls berech­nen, wie viele Tage zurück gerech­net wer­den müssen. Dazu erstellen Sie wiederum eine (Benutzerdefinierte) Hil­f­ss­palte, in welch­er sie den numerischen Wochen­tag des Quar­talsendes fes­tle­gen; allerd­ings ist für diese Berech­nung nicht der Son­ntag (US-Stan­dard) oder Mon­tag (EU) der 1. Tag der Woche, son­dern der Fre­itag. Als neuen Spal­tenna­men ver­wende ich WT und füge dann diese Benutzerdefinierte Spal­tenformel ein:
= Date.DayOfWeek([Quartalsende], Day.Friday)
was fol­gen­des Ergeb­nis zeit­igt, wenn Sie der Pow­er Query-Regel fol­gen, dass alle Formeln in exakt der vorgegebe­nen Groß- / Klein­schrei­bung eingegeben wer­den müssen:

Die berech­neten Dif­ferenz-Tage, siehe let­zte Spalte „WT”

Sie wis­sen nun, welche Anzahl von Tagen vom Quar­talsende abge­zo­gen wer­den müssen, um den let­zten Fre­itag des Quar­tals zu berech­nen. Bleibt die Frage, wie Sie das mit PQ umset­zen kön­nen. Es wird Sie nicht ver­wun­dern, dass auch hier eine neue, Benutzerdefinierte Spalte der Weg zum Ziel ist. Und „natür­lich” ist auch da eine Formel ange­sagt. Die Über­schrift kann  Let­zter Fre­itag / Qrt. sein und als Formel schreiben Sie:
= Date.AddDays([Quartalsende], -[WT])
(bitte Groß- Klein­schrei­bung beacht­en) und endlich ist ist nun das ersehnte Datum in ein­er eige­nen Spalte gegeben. Diese Spalte noch als Datum zu for­matieren, kann dur­chaus eine gute Idee sein. 😎 

Als let­zten Schritt markieren Sie die Spal­ten Quar­tal und Let­zter Fre­itag / Qrt. und nach einem Recht­sklick in eine der bei­den Über­schriften wer­den sie Andere Spal­ten ent­fer­nen. Bleibt noch Schließen & laden oder Schließen & laden in… um das Ergeb­nis entwed­er in einem neuen Tabel­len­blatt oder an ein­er anzugeben­den Wun­sch­po­si­tion als For­matierte Tabelle zu spe­ich­ern.

Zugegeben, ich habe diese Lösung mit „Tripelschrit­ten” erstellt. Den einen oder anderen Schritt kön­nte ich in ein­er einzi­gen Formel mit mehreren Funk­tio­nen zusam­men­fassen. Und ein großer Teil der vom­ir vorgenomme­nen Umbe­nen­nung der Über­schriften ist in erster Lin­ie nur für Ein­steiger gedacht. Da dieses alles aber nicht unbe­d­ingt einen spür­baren Zeit­gewinn im späteren Ablauf bringt, finde ich solch eine Vorge­hensweise bess­er. Sie ist trans­par­enter, ein­fach­er nachvol­lziehbar und natür­lich auch bei Bedarf leichter anzu­passen.

Wenn Sie es möcht­en, lässt sich das natür­lich auf jeden beliebi­gen Wochen­tag trans­formieren. Entwed­er sie ändern in der Funk­tion den Wochen­tag oder sie leg­en in einem Arbeits­blatt eine kleine Tabelle an, wo Pow­er Query den gewün­scht­en Wochen­tag ausle­sen und entsprechend auswerten kann. Diese Funk­tion­al­ität soll aber hier nicht disku­tiert wer­den, fra­gen Sie aber gerne wegen ein­er kostenpflichti­gen Lösung an.

Hin­weis: Meine endgültige Lösung auf­bauend auf der hier gezeigten Vorge­hensweise kön­nen Sie hier herun­ter­laden. Eine stark an ein fast schon fer­tig erstelltes Video angelehnte Ver­sion der *.xlsx (mit weniger Formeln) ste­ht Ihnen hier zum Down­load bere­it.

▲ nach oben …

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

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 2,00 € freuen …

Ref­er­ence: #2416