In einem Forum wurde fol­gende Frage aufge­wor­fen: In ein­er aus dem Inter­net importierten Liste von Aktienkursen soll jew­eils der let­zte Han­del­stag des Monats gelöscht wer­den. Einen kleinen Auszug der Dat­en find­en Sie als Excel-Datei hier zum Down­load, die zu löschen kalen­darischen Dat­en sind von mir rot und fett markiert wor­den. Dieser Umweg des spe­ich­erns als File war prinzip­iell darum erforder­lich, weil in dem Beitrag die wirk­liche Daten­quelle (Link) aus für mich nicht nachvol­lziehbaren Grün­den anonymisiert wor­den ist. Außer­dem halte ich es für wichtig, dass Sie bei den Abbil­dun­gen iden­tis­che Dat­en auf ihrem Bild­schirm sehen.

Hin­weis: Der Fragesteller hat wohl zwis­chen­zeitlich seine Frage „angepasst” 🙄 und jet­zt die aus mein­er Sicht logis­chere Forderung aufgezeigt, dass nur die Schlusskurse aus­gew­ertet wer­den sollen. Auch wenn ja bere­its die Abfrage Tabelle1 (2) existiert und diese schein­bar nur weit­er ver­wen­det wer­den kann, so sieht die Real­ität doch etwas anders aus …

Ich habe für diese geforderten Monat­sende-Kurse eine weit­ere Datei erstellt, wo ich auch mit Pow­er Query genau die gewün­scht­en Werte her­aus­fil­tere. Ich bin bewusst einen anderen Weg gegan­gen, um zum Ziel zu gelan­gen. Excel (entsprechend auch Pow­er Query) lebt von und mit der Vielfalt. 😎 Eine weit­ere Beson­der­heit der dort enthal­te­nen Abfrage ist: Der let­zte Wert des aktuellen Monats wird automa­tisch nicht berück­sichtigt, also gelöscht. Es wäre zwar möglich, den let­zten Han­del­stag eines Monats zu berech­nen und mit dem höch­sten einge­tra­ge­nen Datum des Monats ver­gle­ichen, aber das ist aus mein­er Sicht den Aufwand nicht wert, weil spätestens mit dem näch­sten Han­del­stag nach Aktu­al­isierung der Abfrage alles wieder seine Richtigkeit hat. Eine kurze Beschrei­bung zur Vorge­hensweise sowie den Link zu der Datei find­en Sie hier in diesem Beitrag.

▲ nach oben …

Lösung (1)

Ihnen ist gewiss aufge­fall­en, dass der let­zte Han­del­stag nicht in jedem Fall der Let­zte des Monats ist. Es soll also nicht der Ulti­mo, son­dern die Zeile mit dem jew­eils let­zten Datum des Monats gelöscht wer­den. Im diesem Beitrag soll nicht der 15. Feb­ru­ar 2018 gelöscht wer­den, obwohl es hier ja das höch­ste einge­tra­gene Datum des Monats ist; es ist anzunehmen, dass für diesen Monat noch Werte für weit­ere Tage fol­gen wer­den.

Nach dem Import der Dat­en ist es Pow­er Query ja egal, woher die Dat­en kom­men. In der Real­ität wer­den sie direkt aus dem World Wide Web geholt wor­den sein, hier haben Sie diese aus der vor­bere­it­eten Datei geholt. Ich erwähne dieses, weil ich bei ein­er Daten­quelle „File” ver­mut­lich bere­its im Arbeits­blatt ein­er Hil­f­ss­palte mit Monat und Jahr anle­gen würde; bei dem Direkt-Import aus dem Netz ist dieses naturgemäß nicht möglich. Hier muss das in Pow­er Query selb­st geschehen.

Die Dat­en sind ‑auf welchem Wege auch immer- in Pow­er Query in den Abfrage-Edi­tor importiert. Die Spalte Datum wird ver­mut­lich als Daten­typ Datum/Uhrzeit for­matiert sein; in dem Fall wer­den sie als ersten Schritt dieser Spalte den Daten­typ Datum zuweisen. Anschließend erstellen Sie (in Pow­er Query) die bere­its ange­sproch­ene Hil­f­ss­palte, wo Monat und Jahr im For­mat M/JJJJ einge­tra­gen wer­den soll. Dazu wählen Sie erst ein­mal das Menü Spalte hinzufü­gen und dort ein Klick auf Benutzerdefinierte Spalte. Als Neuer Spal­tenname kön­nen Sie es bei Benutzerdefiniert belassen, ich ziehe M/JJJJ oder eine ähn­liche Über­schrift vor. Als Benutzerdefinierte Spal­tenformel geben Sie in das Textfeld nach dem Gle­ich­heit­sze­ichen fol­gende Formel ein:
Text.From(Date.Month([Datum]))&"/"&Text.From(Date.Year([Datum]))

Nutzen Sie jet­zt die Gele­gen­heit, den Zwis­chen­stand der Abfrage so zu spe­ich­ern, dass nur eine Verbindung existiert: Dateien | Schließen & laden | Schließen & laden in… und dann Nur Verbindung anklick­en. Öff­nen Sie nun wieder die Abfrage. Das Ziel ist, eine neue Abfrage zu erstellen, wo auss­chließlich die let­zten Datum­sein­träge jedes Monats (mit der bere­its ange­sproch­enen Aus­nahme), also die zu löschen­den Zeilen enthal­ten sind. Dazu gehen Sie diesen Weg: Start | Ver­wal­ten | Duplizieren. Dadurch wird ein Dup­likat der existieren­den Abfrage mit dem Namen Tabelle1 (2) erstellt. Hier wer­den Sie nun die entsprechen­den kalen­darischen Dat­en fil­tern. Begin­nen Sie mit Start | Grup­pieren nach. Im Feld Grup­pieren nach wählen Sie die Spalte M/JJJJ (oder entsprechend den von Ihnen ver­wen­de­ten Namen), bei Neuer Spal­tenname bietet sich beispiel­sweise Max­i­mum an und bei Vor­gang wer­den sie entsprechend Max. Und das Max­i­mum soll natür­lich von der Spalte Datum gefun­den und gefiltert wer­den. Diese Abfrage stellt sich nun so dar:

Die gefilterten Dat­en

An dieser Stelle stellt sich fol­gende Frage: Soll die 1. Zeile (hier der 15.02. 2018) auch bei späteren Aktu­al­isierun­gen in der Ergeb­nis-Tabelle verbleiben oder soll sie generell gelöscht wer­den? Ich gehe davon aus, dass dieser Tag nicht in der Tabelle verbleiben soll, darum Start | Zeilen ver­ringern | Zeilen ent­fer­nen | Erste Zeilen ent­fer­nen 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 wer­den sollen.

Wech­seln Sie zur Abfrage Tabelle1. Da Sie sich ja immer noch im Start-Menüband befind­en, ein Klick auf Kom­binieren | Abfra­gen zusam­men­führen  durch einen Klick auf das Dreieck erweit­ern und Abfra­gen als neue Abfrage zusam­men­führen. Es öffnet sich dieser Dia­log:

In dem markierten Feld die zweite Abfrage wählen

Wählen Sie im leeren Drop­down (im Bild markiert) die einzig verbleibende Abfrage Tabelle1 (2). Klick­en Sie nun in bei­den Bere­ichen auf ein beliebiges Feld in der jew­eili­gen Spalte M/JJJJ und wählen Sie im Bere­ich Join-Art den Ein­trag Link­er Anti-Join:

Hier bes­tim­men Sie die Art der ZUsammenführung/Filterung

Vor einem Klick auf OK kon­trol­lieren Sie den Infor­ma­tion-Text links dieser Schalt­fläche wo angezeigt wird, dass 35 der ersten 39 Zeilen über­nom­men wer­den. Das entspricht den Anforderun­gen. Die nun erstellte Abfrage enthält eine neue Spalte mit der Über­schrift Tabelle1 (2), wo in jed­er Zeile der Inhalt Table ste­ht. In den meis­ten Fällen wer­den Sie diese Spalte erweit­ern und auswerten, hier ist das jedoch nicht rel­e­vant. Markieren Sie die bei­den let­zten Spal­ten und löschen Sie diese auf eine Ihnen genehme Weise.

Damit ist die Auf­gabe beina­he erfüllt. Klick­en Sie auf den Text Schließen & laden unter­halb des Sym­bols im Menü Start oder gehen Sie über den Menüpunkt Datei, anschließend Schließen & laden in… und acht­en Sie darauf, dass als Ziel Tabelle angegeben ist. Wenn Sie die Dat­en aus dem Netz geholt haben, soll­ten Sie an dieser Stelle Beste­hen­des Arbeits­blatt und dort beispiel­sweise die Zelle $A$1 auswählen und anschließend Laden. Das automa­tisch erstellte Arbeits­blatt Tabelle2 kön­nen Sie gerne löschen oder ver­steck­en, da sie dieses im Nor­mal­fall nicht brauchen. Es spricht auch nichts dage­gen, wenn Sie im recht­en Seit­en­fen­ster des Excel-Arbeits­blatts einen Recht­sklick auf die Abfrage Tabelle1 (2) durch­führen und dann Laden in… | Nur Verbindung erstellen. – Am näch­sten Han­del­stag genügt ein Klick auf Aktu­al­isieren, um die Zahlen auf den neuesten Stand zu brin­gen.

▲ nach oben …

Alternativ-Lösung für Monatsende-Kurse

Dieser Part ist nicht ganz so aus­führlich wie der vorherige, aber da die Lösung in dieser Datei enthal­ten ist kön­nen Sie anhand der aufge­führten Stich­punk­te und der Liste in Angewen­dete Schritte (rechte Seit­en­fen­ster) gewiss gut nachvol­lziehen, wie sie zum Ziel gelan­gen. Hier nun stich­wor­tar­tig die Vorge­hensweise:

• Öff­nen oder importieren Sie die Quell­datei (hier).
• Erforder­lichen­falls die Dat­en in den Abfrage-Edi­tor importieren.
• Schließen & laden | Schließen & laden in… | Nur Verbindung erstellen.
• Ändern Sie den Daten­typ der Spalte Datum auf (nur) Datum.
• Die Abfrage wieder öff­nen, Gruppe Abfrage | Dup­likat.
• 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ü Trans­formieren | Gruppe Textspalte | Spal­ten zusam­men­führen.
• Als Trennze­ichen wählen Sie Benutzerdefiniert und tra­gen in das leere Textfeld darunter einen Schrägstrich / ein.
• Mit OK bestäti­gen.
• Menü Spalte hinzufü­gen | Benutzerdefinierte Spalte.
• Neuer Spal­tenname: eventuell Aktueller Monat ein­tra­gen (nicht erforder­lich, sorgt aber für Trans­parenz).
• Als Benutzerdefinierte Spal­tenformel 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 vorgenom­men haben) Zeilen so fil­tern, dass nur die FALSE – Werte ste­hen bleiben (bei TRUE also das Häkchen ent­fer­nen).
• Die Spalte Aktueller Monat löschen (Zusam­menge­führt ste­hen lassen).
• Menü Start | Gruppe Trans­formieren | Grup­pieren nach
  • Grup­pieren nach: Zusam­menge­führt
  • Neuer Spal­tenname: Datum
  • Vor­gang: Max
  • Spalte: Datum
  • Mit OK bestäti­gen.
• Spalte Zusam­menge­führt löschen.
• Kom­binieren | Abfra­gen zusam­men­führen
• Im unteren Fen­ster­bere­ich die Abfrage Tabelle1 auswählen.
• In bei­den Abfra­gen ein Klick in die Dat­en der Spalte Datum.
• Join-Art so belassen, dann mit OK bestäti­gen.
• Über­schrift Tabelle1 durch Klick auf den Dop­pelpfeil  erweit­ern.
• Die Häkchen bei Datum und Ursprünglichen Spal­tenna­men … ent­fer­nen, OK.
• Schließen & laden und bei Bedarf die Ergeb­nista­belle zu der Wun­sch­po­si­tion ver­schieben.

Das war es auch schon. Auch hier gilt natür­lich, dass ein Klick auf Aktu­al­isieren die Dat­en in der Ergeb­nis-Tabelle auf den neuesten Stand bringt.

▲ nach oben …