Welches Datum ist das Naheste zu einem Referenz-Datum?

Xtract: Mittels Power Query das Datum der letzten Aktivität vor (beispielsweise) einem Vertragsabschluss und die daraus resultierenden Differenztage berechnen.

  Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ   

Differenz-Tage berechnen und letztes Datum vor einem Referenzdatum erkennen

In einem Forum wurde die Aufgabe dargelegt, dass zu einem bestehenden Referenz-Datum (Spalte A) das nächstgelegene frühere Datum gefunden werden sollte. Im Endeffekt soll die Zeitspanne zwischen der letzten Aktivität vor einem Abschuß und der Vertrags-Unterschrift selbst berechnet und auf der Basis dieser Differenz die Tage der letzten Aktivität durch eine farbliche Hinterlegung gekennzeichnet werden. Ich zitiere hier einfach einmal einen Teil des Eröffnugs-Beitrages:

… ich habe eine Herausforderung und hoffe, hier Hilfe zu finden. Ich habe einen sehr großen Rohdatensatz mit sehr vielen Daten. Es gibt pro Zeile jeweils ein Datum einer Zusage auf eine Einladung und es soll rausgefunden werden, welche Maßnahme dafür verantwortlich war. Es gibt sowohl Anruf als auch Briefe, wobei auch beides noch nach der Zusage stattfinden kann. Zum Einen soll somit das letzte Datum vor Zusage farbig markiert werden und zum anderen in einer neuen Spalte berechnet werden, wie viel Zeit zwischen der letzten Maßnahme vor Zusage und Zusage verstrichen sind. Ist das irgendwie möglich?

Und ja, es gibt mehrere Möglichkeiten. Ich habe hier bewusst den Weg mit Power Query beschritten. Insbesondere wegen des Hinweises, dass es sich um einen sehr großen Rohdatensatz handelt. Und es hatte mich auch gereizt, weil bis zu meinem ersten Beitrag in diesem Thread niemand auch nur in die Richtung PQ „geblinzelt“ hat. 😉

Bleibt an dieser Stelle noch der Verweis auf die Musterdatei mit der Wunschlösung, die Sie hier (als Kopie des Originals) herunterladen können. Und falls Sie jetzt am überlegen sind, wie ich die Zellen mit den Treffern so „schön“ markieren werde: Nein, das geht nicht mit Power Query, das passiert in der Endrunde per Benutzerdefinierte Formatierung. Oder alternativ eine ganz andere Lösung, die ich Ihnen in diesem Beitrag auch aufzeige.

Betrachten Sie zu Beginn erst einmal die Muster-Lösung, welche vom Fragesteller teilweise von Hand erstellt worden ist:

Die Muster-Daten, teils von Hand berechnet und farblich hervorgehoben

Sie wissen vielleicht, dass eine ordentliche Tabelle nur 1 Überschrift-Zeile hat, die naturgemäß über mehrere Spalten gehen kann. Darum werden Sie für den Import ausschließlich den Bereich mit den Daten von A2:H8 markieren und beispielsweise per StrgT oder StrgL als „Intelligente“ Tabelle formatieren. Sorgen Sie anschließend dafür, dass eine beliebige Zelle innerhalb der Tabelle (oder auch die komplette Tabelle) markiert ist. Gehen Sie über Daten | Aus Tabelle/Bereich und die Daten werden umgehend in den Power Query-Editor importiert.¿  Danach wird Ihnen gewiss auffallen, dass alle kalendarischen Daten automatisch mit der Uhrzeit 00:00:00 versehen worden sind.

Nicht nur aus Gründen der Optik sondern auch um einen sinnvollen Datentyp zu haben, sollten alle Datum-Spalten zu einem (Nur-) Datum umgewandelt werden. Dazu klicken Sie in die Überschrift der ersten Spalte Datum Zusage, Shift und ein Klick in die letzte Überschrift Datum 2. Brief. Nun sind alle Spalten der Abfrage-Tabelle markiert. Strg und ein Klick in die Überschrift der 2. Spalte (Zeit zwischen…) und diese Spalte wird von der Markierung ausgenommen. Nun im Menü Start | Datentyp: Datum/Uhrzeit ▼ erweitern und im Kontextmenü ein Klick auf Datum. Bestätigen Sie die Sicherheitsabfrage und in den entsprechenden Spalten steht das reine Datum mit dem entsprechenden Datentyp.

Nun soll Power Query irgendwie feststellen, welches das letzte Datum in der jeweiligen Zeile ist, welches noch vor dem Datum in Datum Zusage oder auch am gleichen Tag ist. Alle anderen kalendarischen Werte der jeweiligen Zeile sind nicht relevant. Um diesen Stichtag heraus zu bekommen gehen Sie einen kleinen Umweg. 😎 

Normalerweise wird sich der Editor so darstellen, dass links der Daten-Tabelle das Wort Abfragen senkrecht dargestellt ist. Klicken Sie auf dieses Wort und der schmale Bereich wird sich zu einem kompletten Seitenfenster erweitern.¿ Und das stellt sich im Anschluss so dar:

Das linke Seitenfenster zeigt alle existierenden Abfragen dies Files auf

Prinzipiell finde ich den Tabellen- bzw. Abfragenamen Tabelle1 (oder ähnlich) einfach zu nichtssagend. Darum klicke ich entweder im linken oder rechten Seitenfenster auf den Namen der Abfrage, F2 oder Rechtsklick und Umbenennen und vergebe dann einen sinnvollen Namen; ich ziehe die internationale Bezeichnung RawData (Rohdaten) vor, Sie können natürlich einen beliebigen Namen wählen. 😎 Bei der Gelegenheit: Ich vergebe meist bereits bei der Erstellung/Formatierung der Intelligenten Tabelle in Excel einen sinnvollen Namen für diesen Bereich; dieser Name wird dann automatisch als Abfrage-Name in PQ übernommen.

▲ nach oben …

So weit die Vorbereitungen. Und es ist an der Zeit, sich einmal mit den Werten in der 2. Spalte auseinander zu setzen. Prinzipiell sind das ja jene Ergebnisse, die zur Ermittlung des am nahest liegenden Datum zum Tag der Zusage liegen. Und wie wurden in diesem Fall die Werte in Excel berechnet? Gute Frage, einfach kurz einmal nachsehen… Also ein Klick in das Fenster der ursprünglichen Excel-Tabelle und … es tut sich nichts. 🙄

Des Rätsels Lösung: Die Power Query-Abfrage muss zuerst geschlossen werden. Ich ziehe es vor, sie „unsichtbar“ zu schließen, also die bisherigen Ergebnisse nicht in eine Excel-Tabelle zu schreiben sondern im Speicher zu behalten. Dazu gehe ich diesen Weg: Klick in den Text Schließen & laden (oder Menüpunkt Datei), dann Schließen & laden in… und Nur Verbindung erstellen.

Die Abfrage wird geschlossen und Sie haben vollen Zugriff auf das Arbeitsblatt. Spätestens jetzt erkennen Sie, dass die Werte in Spalte B von Hand eingetragen worden sind. Und in B1 ist ja auch vermerkt, dass genau dieser hier von Hand eingetragene Wert per Formel berechnet werden soll. Na gut, Power Query kann das auch sehr gut und auch das ist ja schließlich das Ziel dieser Übung. 😉 Aber erst einmal lassen Sie nach dem wieder öffnen der Power Query-Abfrage die Spalte mit den händisch berechneten Werten zu Vergleichszwecken bestehen. Ich mache nun die Spalte sehr schmal, um in der normalen Ansicht mehr von den restlichen Daten zu sehen. Dass dann von der Überschrift nur noch Fragmente zu sehen sind, ist ohne Bedeutung.

Markieren Sie nun durch einen Klick die Überschrift Datum Zusage, Shift oder Strg und danach die zweite Spalte. Einen Rechtsklick in eine der beiden Überschriften und wählen Sie im Kontextmenu die Zeile Andere Spalten entpivotieren:

Nach dem Entpivotieren werden die Daten deutlich anders dargestellt

Die Abfrage besteht nun nur noch aus 4 Spalten, ist aber wegen erheblich mehr Zeilen deutlich länger geworden. Die Inhalte sind nur anders verteilt. Um zu gewährleisten, dass später in jedem Falle genau diese Reihenfolge eingehalten wird, klicken Sie nun im Menü auf  Spalte hinzufügen und wählen im Menüband Indexspalte. Automatisch wird eine neue Spalte erzeugt und ein mit 0 (null) beginnender Index generiert. Bleiben Sie in diesem Menüpunkt und klicken Sie auf das Symbol Benutzerdefinierte Spalte. Es öffnet sich ein Dialogfenster wo Sie in das Feld Neuer Spaltenname beispielsweise den Wert Check eintragen. In das Feld Benutzerdefinierte Spaltenformel kommt nun eine Formel mit welche sie bestimmen, ob der Wert in der Spalte Wert kleiner oder eben nicht kleiner dem in der Spalte Datum Zusage ist. Geben Sie dazu diese Formel nach dem Gleichheitszeichen ein:

= [Wert] <= [Datum Zusage]¿

… und als Ergebnis wird je nach Auswertung des Vergleichs entweder True oder False ausgegeben. True bedeutet, dass das Datum in der Spalte Wert anschließend ausgewertet werden soll. Erweitern Sie die Überschrift Check und entfernen Sie im Dialog das Häkchen bei FALSE, um nach TRUE zu filtern. Die Abfrage verkleinert sich dadurch von 36 auf 31 Zeilen.

Löschen Sie die Spalte Check beispielsweise über das Kontextmenü oder nach Markierung der Überschrift durch Klick auf die Taste Entf, denn sie hat ihre Schuldigkeit getan. 💡 

Markieren Sie die Spalte Datum Zusage. Auch wenn diese bereits aufsteigend sortiert ist: Sortieren Sie noch einmal explizit Aufsteigend, damit diese Sortierung Vorrang hat. Nun ein Klick in Wert, wo ja das Datum der jeweiligen Aktion vermerkt ist. Erweitern Sie die Überschrift und wählen Absteigend sortieren.


Machen Sie gerne an dieser Stelle eine gedankliche Pause. „Sortieren“ Sie für sich selber, was bis zu diesem Punkt geschaffen, erreicht worden ist. Machen Sie sich klar, dass immer noch 31 statt 7 Zeilen in der Abfrage enthalten sind.

Markieren Sie (der besseren Erkennbarkeit wegen) nun zuerst die Spalte Datum Zusage und dann per Strg die Spalte Wert. Vergleichen Sie nun in der jeweils ersten Zeile jedes Datums der ersten Spalte mit den dazu gehörenden Werten der Spalte Wert. Sie werden erkennen, dass in Zeile 1, Zeile 6, Zeile 10, … jeweils das jüngste, das am nächsten liegende Datum in Relation zu Datum Zusage stimmig ist. Was liegt also näher, als in der ersten Spalte Duplikate entfernen auszulösen?

Kennen Sie den Satz: „Unverhofft kommt oft“? Das trifft gewiss auch hier zu. Die Ergebnisse sind gewiss irritierend:

… das entspricht gewiss nicht Ihren Erwartungen

Wieso das? Wenn Sie schon etwas PQ-Erfahrung mit dem löschen von Duplikaten haben werden Sie davon ausgehen, dass in der markierten Spalte stets die erste Zeile erhalten bleibt und die darauf folgenden Duplikate (in den darunter liegenden Zeilen) gelöscht werden. Aber das passt hier ja nun ganz und gar nicht. Woran liegt die unerwünschte, prinzipiell falsche Löschung?

Die Spalte Index sollte Ihnen einen deutlichen Hinweis geben. Offensichtlich hat Power Query erst nach dem Index sortiert und dann die Duplikate entfernt. 😕 Und ich versichere Ihnen: Wenn Sie diesen Schritt im rechten Seitenfenster bei Angewendete Schritte entfernen, werden auch die richtigen, gewünschten Duplikate entfernt. Eine Möglichkeit… aber zugegeben, das kann es doch nicht sein! Schließlich wurde die Spalte Index eingefügt, um später wieder exakt die gewünschte Sortierung der Zeilen zu erhalten.

Falls Sie den Schritt mit der Index-Spalte aus Erprobungsgründen nachvollzogen haben, fügen Sie an der entsprechenden Stelle wieder eine Index-Spalte ein. – Und natürlich gibt es einen Ausweg aus diesem Dilemma. Klicken Sie im rechten Seitenfenster auf den vorletzten Eintrag Sortierte Zeilen, im Hauptmenü Spalte hinzufügen | Indexspalte, bestätigen Sie im Dialog, dass eine Zeile eingefügt werden soll und es wird eine weitere Spalte Index.1 erstellt. Wenn Sie im rechten Seitenfenster nun auf die letzte Zeile Entfernte Duplikate klicken werden sie gewiss voller Freude feststellen, dass jetzt die kalendarischen Daten in der Spalte Wert korrekt sind. 💡

Die zweite Index-Spalte hat ihren Dienst getan, darum sollten Sie diese auch löschen. Und da sie nicht genau wissen, ob die Daten bei den folgenden Berechnungen noch einmal umsortiert werden, lassen Sie die Spalte Index erst einmal noch stehen.

Typischerweise sind Sie immer noch im Menü Spalte hinzufügen. Sie erstellen eine Benutzerdefinierte Spalte und vergeben als Überschrift (Neuer Spaltenname) beispielsweise Differenz-Tage. In Excel würden sie nun ganz einfach die Spalte Wert von der Spalte Datum Zusage subtrahieren und Power Query ist da auch ganz „locker“; da bietet sich diese Formel an:

= [Datum Zusage]-[Wert]

Probieren Sie es gerne einmal aus und das Ergebnis wird sie höchstwahrscheinlich deutlich überraschenden:

Irgendwie sieht das anders aus als erwartet… 😯

Irgendwie stimmt das Ergebnis ja ganz offensichtlich, aber die Darstellung ist alles andere als gewohnt und wahrscheinlich abweichend von einer von Ihnen prognostizierten Lösung. Sie werden -genau wie ich- gewiss nur eine Ganzzahl, einen numerischen Wert erwartet haben. Was sie aber hier sehen ist die Darstellung, welche prinzipiell dem Datentyp Dauer (Duration) entspricht. Und ja, der eigentliche Datentyp ist Beliebig, warum auch immer…

Die einfachste Möglichkeit wäre, es bei dieser Darstellung zu belassen. Sie verschieben diese Spalte mit der Maus an die zweite Position und vergleichen, ob die Zahl vor dem Punkt mit dem in der (nun) dritten Spalte übereinstimmt. Das trifft zu, also löschen Sie die Spalte Zeit zwischen … Anschließend sollten Sie einfach einmal testen, wie das Ergebnis denn in plain Excel aussieht. Es könnte ja sein, dass sich das schon ganz passabel darstellt. Falls Sie zu Beginn die Abfrage nicht als Nur Verbindung gespeichert hatten gehen Sie beispielsweise über Datei und dort Schließen & laden in… Die Auswahl Tabelle ist okay, markieren Sie Bestehendes Arbeitsblatt und positionieren Sie den Cursor im Blatt Tabelle1 auf die Zelle A12. OK und das sieht dann so aus:

Eigentlich könnte das so bleiben, oder?

Wenn Sie zu Beginn dieser Übung die Abfrage bereits einmal als Nur Verbindung gespeichert hatten, dann werden Sie gewiss nicht sehen, dass eine Tabelle gespeichert werden kann. Schauen Sie einmal hier nach, wie Sie dann vorgehen können, um den hierüber beschriebenen Weg doch zu beschreiten.

Nach meinem Geschmack ist das Ergebnis deutlich transparenter als das Wunschergebnis des Fragestellers im Forum. Und da die Reihenfolge in der Spalte Index zwar stimmt aber nicht in das Ergebnis gehört, würde ich die Abfrage noch einmal öffnen und diese Spalte löschen. Danach noch einmal ein Klick auf Schließen & laden und mein persönliches Ziel wäre erreicht. Aber die Vorgabe war eine andere, darum ist noch etwas mehr Arbeit angesagt. Das einzige „Manko“: Die Überschrift Wert sollte in Power Query noch auf beispielsweise Stichtag angepasst werden.

▲ nach oben …

„Hätte ich doch früher an das zu erreichende Endergebnis und dessen Wunschdarstellung gedacht!“, diesen Satz höre ich doch öfter und ich räume ein, dass auch ich mich in eigenen Projekten davon nicht freisprechen kann. Und Sie werden sich denken können, dass diese Aussage auch hier zutrifft. Aber dank Power Query ist das alles nicht ganz so schlimm, wie es vielleicht ohne dieses Tool wäre. 😉 

Der gravierende Unterschied zu meiner eben vorgestellten Lösung ist, dass in der Wunschlösung praktisch der Originalzustand der Tabelle dargestellt wird (also alle Spalten gezeigt werden) und die jeweiligen geringsten Zeitdifferenzen farblich markiert werden sollen. Wie bereits erwähnt hat die farbliche Hervorhebung aber nichts mit PQ zu tun. Dennoch gilt: Die Gesamtheit der Spalten anzuzeigen, das ist sehr wohl mit oder auch trotz Power Query machbar.

Um das zu erreichen, erstellen Sie erst einmal eine Kopie, ein Duplikat der existierenden Abfrage.¿ Diese Query bekommt dann automatisch den Namen RawData (2). Bleiben Sie in dieser Abfrage und suchen Sie im rechten Seitenfenster jenen Schritt, wo zuletzt alle Spalten dargestellt werden. Wenn Sie noch nicht so routiniert in Power Query sind bietet es sich an, im rechten Seitenfenster von oben nach unten auf die Einträge mit den einzelnen Schritten zu klicken und im Hauptfenster zu beobachten, wann der Wechsel auftritt. Vielleicht werden Sie sich aber auch daran erinnern oder Ihre Erfahrung sagt Ihnen, dass durch das Entpivotieren der Schnitt erfolgte. Also ein Rechtsklick auf diese Zeile und Bis zum Ende löschen auswählen und bestätigen.

Wechseln Sie erforderlichenfalls zum Menüpunkt Start. Löschen Sie als nächsten Schritt die Spalte Zeit zwischen letztem Kontakt und Zusage. Im Menüband wählen Sie Kombinieren | Abfragen zusammenführen und im Dialogfenster wählen Sie dann im einzeiligen Textfeld zwischen den größeren Rahmen die Abfrage RawData. Markeieren Sie nun im oberen als auch unteren „Kasten“ die Spalte Datum Zusage:

Erste Schritt zum Anfügen der neuen Daten

Nach einem OK wird in die Abfrage eine neue Spalte mit dem Namen RawData angefügt. Der Inhalt ist durchgängig Table. Erweitern Sie diese Spalte durch einen Klick auf den Doppelpfeil Doppelpfeil in der Überschrift und entfernen Sie (wirklich) alle Häkchen, ausgenommen bei Differenz-Tage. Ziehen Sie diese Spalte dann an die zweite Position, also nach Datum Zusage.

Ganz zu Beginn haben Sie ja auch der Optik wegen die Spalten mit den kalendarischen Daten als „echtes“ Datum, also ohne Uhrzeit formatiert. Lässt dich das vielleicht auch mit der Spalte Differenz-Tage machen? Aber klar… 😎 Menü (nicht Gruppe) Transformieren | Dauer | Tage und bingo, das Ergebnis stimmt. Und das war’s fast auch schon in Sachen Power Query. Nur noch Schließen & laden und die Tabelle wird in ein neues Tabellenblatt geschrieben. Hinweis: Wahrscheinlich hatten Sie ja zu Beginn die Abfrage RawData schon einmal als Nur Verbindung gespeichert. Au dem Grunde wird dann auch diese Query gnadenlos im gleichen Modus gesichert. Wie Sie dieses gewollte Ergebnis dennoch in ein Tabellenblatt bringen, lesen Sie beispielsweise hier im Blog nach.

▲ nach oben …

Jetzt steht beispielsweise im Blatt Tabelle2 die Tabelle mit den berechneten Differenz-Tagen. Im letzten Schritt sollen jene Zellen markiert werden, welche vom Datum aus gesehen dem letzten Kontakt vor der Zusage entsprechen. Wie schon erwähnt, das geht nicht mit Power Query sondern (sinnvollerweise) mit plain Excel (oder für Freaks unter Ihnen auch mit VBA 😛 ).

Die gespeicherte Tabelle umfasst den Bereich von A1:H7.

  • Wechseln Sie erforderlichenfalls zum Menü Start.
  • Markieren Sie den Bereich C2:H7.
  • Im Menüband Bedingte Formatierung | Neue Regel… | Formel zur Ermittlung der zu formatierenden Zellen verwenden auswählen.
  • Tragen Sie im Dialog diese Formel (mit dem führenden Gleichheitszeichen) in das Textfeld Werte formatieren, für die diese Formel wahr ist:
    =C2+$B2=$A2
  • Klick aus die Schaltfläche Formatieren… und wählen Sie im Register Ausfüllen beispielsweise das prägnante Gelb aus.
  • OK und freuen. 😉 

Das war’s nun wirklich. Die „Aufgabe“ ist mit Power Query gelöst worden. Und ja ich gebe zu, dass dieses oder jenes deutlich „eleganter“ hätte gelöst werden können. Einerseits finde ich, dass Eleganz auf den Laufsteg gehört und andererseits ist es mir wichtig, Ihnen möglichst viel an Basiskenntnissen zu vermitteln. Und manchmal auch die kleinen oder auch größeren Fallstricke aufzuzeigen, denn als Autodidakt weiß ich durchaus, was logisch erscheint aber im Sinne des Power Query keineswegs ist. Und last but not least werden Sie sich in Erinnerung rufen, dass Sie die Index-Spalte gar nicht für die Sortierung gebraucht haben… Aber Solch eine Weisheit erlange auch ich meist erst im Rückblick. Und mir ist dieser eine, zusätzliche und im Nachhinein als unnötig erkannte Schritt lieber als am Schluß festzustellen, dass die Reihenfolge der Daten anders ist als im Ursprung.

▲ nach oben …

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

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50  freuen …

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