Letzten Eintrag/Monat löschen | als einzigen behalten

In einem Forum wurde folgende Frage aufgeworfen: In einer aus dem Internet importierten Liste von Aktienkursen soll jeweils der letzte Handelstag des Monats gelöscht werden. Einen kleinen Auszug der Daten finden Sie als Excel-Datei hier zum Download, die zu löschen kalendarischen Daten sind von mir rot und fett markiert worden. Dieser Umweg des speicherns als File war prinzipiell darum erforderlich, weil in dem Beitrag die wirkliche Datenquelle (Link) aus für mich nicht nachvollziehbaren Gründen anonymisiert worden ist. Außerdem halte ich es für wichtig, dass Sie bei den Abbildungen identische Daten auf ihrem Bildschirm sehen.

Hinweis: Der Fragesteller hat wohl zwischenzeitlich seine Frage „angepasst“ 🙄 und jetzt die aus meiner Sicht logischere Forderung aufgezeigt, dass nur die Schlusskurse ausgewertet werden sollen. Auch wenn ja bereits die Abfrage Tabelle1 (2) existiert und diese scheinbar nur weiter verwendet werden kann, so sieht die Realität doch etwas anders aus …

Ich habe für diese geforderten Monatsende-Kurse eine weitere Datei erstellt, wo ich auch mit Power Query genau die gewünschten Werte herausfiltere. Ich bin bewusst einen anderen Weg gegangen, um zum Ziel zu gelangen. Excel (entsprechend auch Power Query) lebt von und mit der Vielfalt. 😎 Eine weitere Besonderheit der dort enthaltenen Abfrage ist: Der letzte Wert des aktuellen Monats wird automatisch nicht berücksichtigt, also gelöscht. Es wäre zwar möglich, den letzten Handelstag eines Monats zu berechnen und mit dem höchsten eingetragenen Datum des Monats vergleichen, aber das ist aus meiner Sicht den Aufwand nicht wert, weil spätestens mit dem nächsten Handelstag nach Aktualisierung der Abfrage alles wieder seine Richtigkeit hat. Eine kurze Beschreibung zur Vorgehensweise sowie den Link zu der Datei finden Sie hier in diesem Beitrag.

▲ nach oben …

Lösung (1)

Ihnen ist gewiss aufgefallen, dass der letzte Handelstag nicht in jedem Fall der Letzte des Monats ist. Es soll also nicht der Ultimo, sondern die Zeile mit dem jeweils letzten Datum des Monats gelöscht werden. Im diesem Beitrag soll nicht der 15. Februar 2018 gelöscht werden, obwohl es hier ja das höchste eingetragene Datum des Monats ist; es ist anzunehmen, dass für diesen Monat noch Werte für weitere Tage folgen werden.

Nach dem Import der Daten ist es Power Query ja egal, woher die Daten kommen. In der Realität werden sie direkt aus dem World Wide Web geholt worden sein, hier haben Sie diese aus der vorbereiteten Datei geholt. Ich erwähne dieses, weil ich bei einer Datenquelle „File“ vermutlich bereits im Arbeitsblatt einer Hilfsspalte mit Monat und Jahr anlegen würde; bei dem Direkt-Import aus dem Netz ist dieses naturgemäß nicht möglich. Hier muss das in Power Query selbst geschehen.

Die Daten sind -auf welchem Wege auch immer- in Power Query in den Abfrage-Editor importiert. Die Spalte Datum wird vermutlich als Datentyp Datum/Uhrzeit formatiert sein; in dem Fall werden sie als ersten Schritt dieser Spalte den Datentyp Datum zuweisen. Anschließend erstellen Sie (in Power Query) die bereits angesprochene Hilfsspalte, wo Monat und Jahr im Format M/JJJJ eingetragen werden soll. Dazu wählen Sie erst einmal das Menü Spalte hinzufügen und dort ein Klick auf Benutzerdefinierte Spalte. Als Neuer Spaltenname können Sie es bei Benutzerdefiniert belassen, ich ziehe M/JJJJ oder eine ähnliche Überschrift vor. Als Benutzerdefinierte Spaltenformel geben Sie in das Textfeld nach dem Gleichheitszeichen folgende Formel ein:
Text.From(Date.Month([Datum]))&"/"&Text.From(Date.Year([Datum]))

Nutzen Sie jetzt die Gelegenheit, den Zwischenstand der Abfrage so zu speichern, dass nur eine Verbindung existiert: Dateien | Schließen & laden | Schließen & laden in… und dann Nur Verbindung anklicken. Öffnen Sie nun wieder die Abfrage. Das Ziel ist, eine neue Abfrage zu erstellen, wo ausschließlich die letzten Datumseinträge jedes Monats (mit der bereits angesprochenen Ausnahme), also die zu löschenden Zeilen enthalten sind. Dazu gehen Sie diesen Weg: Start | Verwalten | Duplizieren. Dadurch wird ein Duplikat der existierenden Abfrage mit dem Namen Tabelle1 (2) erstellt. Hier werden Sie nun die entsprechenden kalendarischen Daten filtern. Beginnen Sie mit Start | Gruppieren nach. Im Feld Gruppieren nach wählen Sie die Spalte M/JJJJ (oder entsprechend den von Ihnen verwendeten Namen), bei Neuer Spaltenname bietet sich beispielsweise Maximum an und bei Vorgang werden sie entsprechend Max. Und das Maximum soll natürlich von der Spalte Datum gefunden und gefiltert werden. Diese Abfrage stellt sich nun so dar:

Die gefilterten Daten

Die gefilterten Daten

An dieser Stelle stellt sich folgende Frage: Soll die 1. Zeile (hier der 15.02. 2018) auch bei späteren Aktualisierungen in der Ergebnis-Tabelle verbleiben oder soll sie generell gelöscht werden? Ich gehe davon aus, dass dieser Tag nicht in der Tabelle verbleiben soll, darum Start | Zeilen verringern | Zeilen entfernen | Erste Zeilen entfernen und geben Sie bei Anzahl von Zeilen den Wert 1 ein. Somit verbleiben nach einem OK nur jene Werte, die aus den importierten Werten gelöscht werden sollen.

Wechseln Sie zur Abfrage Tabelle1. Da Sie sich ja immer noch im Start-Menüband befinden, ein Klick auf Kombinieren | Abfragen zusammenführen  durch einen Klick auf das Dreieck erweitern und Abfragen als neue Abfrage zusammenführen. Es öffnet sich dieser Dialog:

In dem markierten Feld die zweite Abfrage wählen

In dem markierten Feld die zweite Abfrage wählen

Wählen Sie im leeren Dropdown (im Bild markiert) die einzig verbleibende Abfrage Tabelle1 (2). Klicken Sie nun in beiden Bereichen auf ein beliebiges Feld in der jeweiligen Spalte M/JJJJ und wählen Sie im Bereich Join-Art den Eintrag Linker Anti-Join:

Hier bestimmen Sie die Art der ZUsammenführung/Filterung

Hier bestimmen Sie die Art der ZUsammenführung/Filterung

Vor einem Klick auf OK kontrollieren Sie den Information-Text links dieser Schaltfläche wo angezeigt wird, dass 35 der ersten 39 Zeilen übernommen werden. Das entspricht den Anforderungen. Die nun erstellte Abfrage enthält eine neue Spalte mit der Überschrift Tabelle1 (2), wo in jeder Zeile der Inhalt Table steht. In den meisten Fällen werden Sie diese Spalte erweitern und auswerten, hier ist das jedoch nicht relevant. Markieren Sie die beiden letzten Spalten und löschen Sie diese auf eine Ihnen genehme Weise.

Damit ist die Aufgabe beinahe erfüllt. Klicken Sie auf den Text Schließen & laden unterhalb des Symbols im Menü Start oder gehen Sie über den Menüpunkt Datei, anschließend Schließen & laden in… und achten Sie darauf, dass als Ziel Tabelle angegeben ist. Wenn Sie die Daten aus dem Netz geholt haben, sollten Sie an dieser Stelle Bestehendes Arbeitsblatt und dort beispielsweise die Zelle $A$1 auswählen und anschließend Laden. Das automatisch erstellte Arbeitsblatt Tabelle2 können Sie gerne löschen oder verstecken, da sie dieses im Normalfall nicht brauchen. Es spricht auch nichts dagegen, wenn Sie im rechten Seitenfenster des Excel-Arbeitsblatts einen Rechtsklick auf die Abfrage Tabelle1 (2) durchführen und dann Laden in… | Nur Verbindung erstellen. – Am nächsten Handelstag genügt ein Klick auf Aktualisieren, um die Zahlen auf den neuesten Stand zu bringen.

▲ nach oben …

Alternativ-Lösung für Monatsende-Kurse

Dieser Part ist nicht ganz so ausführlich wie der vorherige, aber da die Lösung in dieser Datei enthalten ist können Sie anhand der aufgeführten Stichpunkte und der Liste in Angewendete Schritte (rechte Seitenfenster) gewiss gut nachvollziehen, wie sie zum Ziel gelangen. Hier nun stichwortartig die Vorgehensweise:

  • Öffnen oder importieren Sie die Quelldatei (hier).
  • Erforderlichenfalls die Daten in den Abfrage-Editor importieren.
  • Schließen & laden | Schließen & laden in… | Nur Verbindung erstellen.
  • Ändern Sie den Datentyp der Spalte Datum auf (nur) Datum.
  • Die Abfrage wieder öffnen, Gruppe Abfrage | Duplikat.
  • Menü Spalte hinzufügen | Gruppe Aus Datum & Uhrzeit | Monat | Monat.
  • Wieder Spalte Datum markieren, Menü Spalte hinzufügen | Gruppe Aus Datum & Uhrzeit | Jahr | Jahr.
  • Spalte Monat markieren, Shift, Spalte Jahr markieren.
  • Menü Transformieren | Gruppe Textspalte | Spalten zusammenführen.
  • Als Trennzeichen wählen Sie Benutzerdefiniert und tragen in das leere Textfeld darunter einen Schrägstrich / ein.
  • Mit OK bestätigen.
  • Menü Spalte hinzufügen | Benutzerdefinierte Spalte.
  • Neuer Spaltenname: eventuell Aktueller Monat eintragen (nicht erforderlich, sorgt aber für Transparenz).
  • Als Benutzerdefinierte Spaltenformel geben Sie ein:
    [Zusammengeführt]=Text.From(Date.Month(DateTime.Date(DateTime.LocalNow())))
    &"/"&Text.From(Date.Year(DateTime.Date(DateTime.LocalNow())))
  • Spalte Aktueller Monat (oder Benutzerdefiniert, falls Sie die Änderung nicht vorgenommen haben) Zeilen so filtern, dass nur die FALSE – Werte stehen bleiben (bei TRUE also das Häkchen entfernen).
  • Die Spalte Aktueller Monat löschen (Zusammengeführt stehen lassen).
  • Menü Start | Gruppe Transformieren | Gruppieren nach
    • Gruppieren nach: Zusammengeführt
    • Neuer Spaltenname: Datum
    • Vorgang: Max
    • Spalte: Datum
    • Mit OK bestätigen.
  • Spalte Zusammengeführt löschen.
  • Kombinieren | Abfragen zusammenführen
  • Im unteren Fensterbereich die Abfrage Tabelle1 auswählen.
  • In beiden Abfragen ein Klick in die Daten der Spalte Datum.
  • Join-Art so belassen, dann mit OK bestätigen.
  • Überschrift Tabelle1 durch Klick auf den Doppelpfeil Doppelpfeil erweitern.
  • Die Häkchen bei Datum und Ursprünglichen Spaltennamen … entfernen, OK.
  • Schließen & laden und bei Bedarf die Ergebnistabelle zu der Wunschposition verschieben.

Das war es auch schon. Auch hier gilt natürlich, dass ein Klick auf Aktualisieren die Daten in der Ergebnis-Tabelle auf den neuesten Stand bringt.

▲ nach oben …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Datum & Zeit, Filtern & Sortieren, Power Query, PQ-Quickies abgelegt und mit , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.