Und täglich eine neue Liste…

Und täglich eine neue Liste

Kompletter Beitrag für Spender

Im Ablauf vieler Unternehmen wird beispielsweise durch ein ERP-System an jedem Arbeitstag eine neue Liste im *.xlsx- oder *.csv-Format mit den unterschiedlichsten Daten erstellt. Und der Übersicht wegen liegen diese Files auch nicht in einem einzigen Ordner sondern oft nach Monaten bzw. nach Jahr und Monat getrennt. Die Verzeichnisnamen tragen dann beispielsweise den Monatsnamen, die Monatszahl oder das Datum in irgendeiner Form; die Files haben dann in der Regel auch das Datum in der Form 2017-08-13, mitunter aber auch mit einem vorangestellten oder angehängten Zusatz.

In der Praxis sieht es nun oft so aus, dass ein Mal der bisherige Stand in einer Exceltabelle zusammengestellt und danach die durch das System täglich neu erstellte Datei per copy/paste angefügt wird. Ich kenne genügend Fälle, wobei durch Unachtsamkeit die Daten eines Tages doppelt angefügt worden sind und dadurch natürlich das Bild total verfälscht war. Es sind auch schon einmal Daten nicht eingepflegt worden, weil in Köln zwar der Rosenmontag ein arbeitsfreier Tag war in Berlin aber durchaus gearbeitet wurde und dort natürlich ein Report für die Zentrale in Köln erstellt worden ist.

Excel bietet mit Power Query eine hervorragende Möglichkeit, diesen Vorgang zu automatisieren. Dabei wird der gesamte Datenbestand eines Verzeichnisses (Mappe, Folder) eingelesen und nach einem Klick auf Aktualisieren auch auf den neuesten Stand gebracht. Eine sehr ausführliche Anleitung finden Sie für Quelldateien des Typs *.xlsx hier im Blog, für CSV-Dateien an dieser Stelle.

▲ nach oben …

Erste Schritte

Hinweis: Zu diesem Beitrag existiert auch ein kleines Video, wo manches anschaulicher und in einzelnen Teilen auch etwas differenzierter dargestellt wird. Laden Sie dieses Medium hier von unserem Server herunter.

Meine Muster-Datei baut darauf auf, dass die Quelldaten in einem definierten Verzeichnis liegen. Gleichermaßen haben die Excel-Dateien von der Namensgebung her auch einen eindeutigen, durchgängigen Aufbau, ohne „Ausreißer“. Wenn Sie also die von mir erstellte fertige Datei verwenden wollen, dann werden die Files im Verzeichnis C:\Daten\ liegen und nach dem hier gezeigten Muster weiter untergliedert sein:

Auflistung der im Ordner enthaltenen Files

Sie erkennen in der obigen Abbildung, dass für jeden Monat des Jahres ein Unterverzeichnis (hier: 201707) erstellt wurde und dort die arbeitstäglich erstellten Files gespeichert sind. In der Realität kann es durchaus sein, dass der Übersicht halber auch noch oberhalb dieser Ebene ein weiteres Unterverzeichnis mit dem Jahr erstellt worden ist. In dieser Übung sind jedoch ausschließlich die Verzeichnisse der einzelnen Monate angelegt worden.

Beginnen Sie damit, die Muster-Daten als ZIP-Datei hier herunter zu laden. Nach dem entpacken werden Sie feststellen, dass die Ordnerstruktur bereits existiert und eventuell bei Bedarf im entsprechenden Laufwerk auch so angelegt wird. Erforderlichenfalls sollten Sie das Verzeichnis Daten (mit den Inhalten) nach C:\ verschieben. Das ist wichtig, weil die Power Query-Abfrage den Pfad der zu importierenden Daten speichert. In diesem speziellen Fall ist es sinnvoll, damit Sie die hier gemachten Angaben exakt so übernehmen können. Anschließend öffnen sie in Excel eine leere Arbeitsmappe. Hinweis: Ab hier sind die einzelnen Schritte auch in dem oben angesprochenen kleinen Video dokumentiert. Und beachten Sie bitte, dass die ersten Schritte unter Umständen etwas anders ablaufen können, wenn sie das Power Query als Add-In in Excel 2010 oder der Version 2013 installiert haben.

Im Menü Daten, Gruppe Abrufen und transformieren klicken Sie auf Neue Abfrage | Aus Datei | Aus Ordner. Im darauf erscheinenden Dialog geben Sie entweder von Hand den Ordnerpfad C:\Daten (und nur den!) ein oder lassen die durch einen Klick auf die Schaltfläche Durchsuchen… eintragen:

Darstellung des kompletten Pfades in der Auswahl

Darstellung des kompletten Pfades in der Auswahl

Obwohl sie das oberste Verzeichnis Daten markiert und per OK übernommen haben, werden Ihnen im kommenden Dialog ausschließlich die Daten der in den Unterverzeichnissen enthaltenen Files und nicht eventuelle weitere Unterverzeichnisse angezeigt:

Alle durch Power Query eingelesenen Files auch der Unterverzeichnisse

Alle durch Power Query eingelesenen Files auch der Unterverzeichnisse

Sie prüfen und erkennen, dass es die gewünschten Dateien sind und da sie damit gleich weiterarbeiten wollen, werden sie auf Bearbeiten klicken. Umgehend öffnet sich der Power Query Abfrage-Editor, wo in diesem Fall 61 Dateien mit diversen Informationen in je einer Zeile dargestellt werden.

In vielen Fällen brauchen Sie nur die erste Spalte, um die Inhalte jedes einzelnen Files zu extrahieren. In diesem Fall soll aber das Datum, welches ja im Dateinamen enthalten ist, mit ausgewertet werden. In den eigentlichen Daten ist nämlich kein derartiger Wert enthalten. Da die Spalte Content bereits markiert ist, Shift und ein Klick in die Überschrift Name und beide Spalten sind markiert. Um die nicht markierten Spalten zu löschen, einen Rechtsklick in eine der beiden markierten Überschriften und im Kontextmenü den dritten Punkt Andere Spalten entfernen anklicken. Schon sieht das ganze etwas „aufgeräumter“ aus:

Die auf 2 Spalten reduzierte Abfrage

Die auf 2 Spalten reduzierte Abfrage

▲ nach oben …

Das Datum extrahieren

Es bietet sich an, gleich an dieser Stelle den Dateinamen so umzustellen, dass die Erweiterung entfernt wird. Dann bleibt nämlich nur eine Zeichenfolge übrig, die durch Excel oder Power Query als Datum erkannt werden kann. Dazu klicken Sie in die Überschrift Name und wählen anschließend den Menüpunkt Spalte teilen. Ob sie sich nun dafür entscheiden, Nach Trennzeichen oder Nach Anzahl von Zeichen einzusetzen, bleibt in diesem Fall Ihrem Geschmack überlassen. Grundsätzlich gilt: In jedem Feld gibt es nur einen einzigen Punkt, nämlich den vor der Extension; den könnten Sie als Trennzeichen verwenden sofern gewährleistet ist, dass in jedem Fall ausschließlich das reine Datum (in irgendeiner Form) vor dem Punkt der Extension steht. Das Datum ist hier auch in jeder Zeile identisch aufgebaut, sie brauchen stets die ersten 10 Zeichen, damit nach einer Teilung nur das reine Datum übrig bleibt. Also ist auch dieses gleichwertig. – Hinweis: Wenn entgegen einer ungeschriebenen Regel das Datum in „normaler“ Schreibweise verwendet wird, der Filename also beispielsweise 01.06.2017.xlsx ist, dann ist es natürlich der letzte Punkt, wo das Datum von der Erweiterung abgetrennt werden muss.

Im Begleitvideo habe ich mich für die Anzahl von Zeichen entschieden, darum an dieser Stelle eine kurze Beschreibung des Weges über das Trennzeichen: Sie wählen zu Beginn diese Option und es wird sich solch ein Dialog auf tun:

Das vorgeschlagene Trennzeichen muss geändert werden

Das vorgeschlagene Trennzeichen muss geändert werden

Power Query versucht, ein passendes Trennzeichen zu erkennen. Vielfach stimmt es auch, in diesem Fall aber ist es nicht zielführend. Klicken Sie in das Text-Feld mit dem vorgeschlagenen Minus-Zeichen und ersetzen dieses durch einen Punkt. Aus prinzipiellen Erwägungen werde ich in diesem Fall die Auswahl Beim äußersten linken Trennzeichen anklicken, auch wenn hier nicht die Gefahr besteht, dass ein weiterer Punkt in der Zeichenfolge auftaucht. Mit OK bestätigen und ruckzuck werden sie erkennen, dass die Erweiterung in einer neuen Spalte steht und das Power Query mit der ihm eigenen „Intelligenz“ die Zeichenfolge JJJJ-MM-TT zu einem korrekten Datum umgewandelt hat. Und da es in eine Abfrage nicht mehrere Spalten mit dem gleichen Namen geben kann bzw. darf, wurde die bisherige Überschrift mit einer numerischen Erweiterung angepasst. Löschen Sie nun die Spalte Name.2 über das Kontextmenü oder mittels Entf und benennen Sie die Spalte Name.1 in Datum um; dazu entweder ein Doppelklick in die Überschrift oder über das Kontextmenü oder aber F2. Damit ist eine solide Basis für die nächsten Schritte geschaffen.

▲ nach oben …

Die Daten der Files extrahieren

Die folgenden Schritte sind vielleicht etwas fremdartig für sie, aber wenn sie diese exakt befolgen, werden sie rasch zum gewünschten Ziel gelangen. Immer noch im Abfrage-Editor wählen Sie das Register Spalte hinzufügen. Dort in der Gruppe Allgemein finden Sie das Symbol Benutzerdefinierte Spalte; ein Klick darauf und es tut sich dieser Dialog auf:

Der Dialog für das Zufügen Benutzerdefinierter Spalten

Der Dialog für das Zufügen Benutzerdefinierter Spalten

Auch wenn der Spaltenname ausnahmsweise in diesem Falle völlig unbedeutend ist, vergebe ich einen halbwegs sinnvollen Namen. Statt Benutzerdefiniert trage ich dort wks (Abkürzung für Worksheet) ein. Mit Tab oder einem Mausklick gelange ich in das große Textfenster Benutzerdefinierte Spaltenformel: und gebe dort nach dem = diese Formel in exakt der folgenden Groß- Kleinschreibung ein:
Excel.Workbook([Content])
… und bestätige mit OK. Hinweis: Um Fehler zu vermeiden und auch um es mir einfach zu machen schreibe ich den Formel-Text bis zur ersten öffnenden Klammer und führe dann im rechten Fenster Verfügbare Spalten auf dem Eintrag Content entweder einen Doppelklick aus oder markiere diesen und danach im unteren Bereich ein Klick auf die große Schaltfläche << Einfügen.

In der neu generierten Spalte mit der Überschrift wks erkennen sie, dass jede Zeile eine eigene Tabelle (Table) enthält. Rechts neben der Überschrift sehen Sie ein Symbol mit einem Doppelpfeil Doppelpfeil, worauf sie ein Mal klicken. Es öffnet sich ein Dialogfenster, welches sich dann so darstellt:

Bei den Spalten braucht nur 'Data' markiert zu sein

Bei den Spalten braucht nur ‚Data‘ markiert zu sein

Deaktivieren Sie das Kontrollkästchen (Alle Spalten auswählen). Anschließend aktivieren Sie das Kästchen Data und bestätigen mit OK. In der Abfrage ist auf den ersten Blick keine Änderung sichtbar aber dieses war ein wichtiger Schritt. Fügen Sie nun noch eine Benutzerdefinierte Spalte hinzu und vergeben Spaltennamen beispielsweise Data. Bei Benutzerdefinierte Spaltenformel fügen Sie nun diese Formel ein:
Table.PromoteHeaders([wks.Data])
… wobei sie [wks.Data] aus dem Bereich Verfügbare Spalten übernehmen. In der Spalte Data wiederum ein Klick auf das Erweitern-Symbol Doppelpfeil in der Überschrift und hier werden sie ausschließlich das Kontrollkästchen Ursprünglichen Spaltennamen als Präfix verwenden deaktivieren. Nach einem OK werden automatisch nicht nur diverse neue Spalten erzeugt sondern auch die Daten jedes einzelnen Files entsprechend der Zeilen-Zahl expandiert:

Alle expandierten Spalten sind markiert, die Daten sind komplettiert

Alle expandierten Spalten sind markiert, die Daten sind komplettiert

Jetzt werden sie die Spalten Content und wks.Data löschen und die Abfrage enthält nur noch jene Daten, die auch in einer im weiteren Verlauf zu erzeugenden Tabelle gespeichert werden sollen. Aktivieren Sie dazu das Register Datei und wählen Sie den zweiten Menüpunkt Schließen & laden in… und klicken Sie auf die Options Schaltfläche Bestehendes Arbeitsblatt. Die Vorgabe $A$1 kann bestehen bleiben, wenn sie die Daten in die Zelle A1 einfügen wollen. Ein Klick auf Laden und die Daten werden so wie seitens Power Query importiert in eine Tabelle/Liste geschrieben. Sicherlich werden sie nun noch die Spalten D: K als Währung formatieren.

Wenn Sie diese Liste nun als Basis für eine Pivot-Tabelle verwenden werden Sie sich vielleicht darüber wundern, dass die Spalten Unit und Gruppe automatisch in den Bereich Werte abgelegt werden, wenn sie bei den Feldern (nur) das Häkchen aktivieren und die Felder nicht in die Ziel-Gruppe ziehen. Das liegt daran, dass diese Werte als Zahl formatiert sind. Wollen Sie das vermeiden, dann öffnen Sie noch einmal die Abfrage und weisen den beiden Spalten den Typ Text zu. Und bei der Gelegenheit: So ganz optimal ist der Aufbau der Daten nicht immer für eine PivotTable geeignet. Hier im Blog können Sie nachlesen, wie sie solch eine Tabelle (auch mit Power Query) optimieren können.

▲ nach oben …

Zu viel Aufwand?

Zugegeben, manch einer wird sich fragen, ob der Aufwand für eine einmalige Aktion gerechtfertigt ist. Ich denke: Selbst für eine einmalige Aktion lohnt es sich, denn Power Query nimmt Ihnen hier einiges an Arbeit ab und vermeidet auch Fehler. Denn vielleicht ist Ihnen aufgefallen, dass es auch noch einen Ordner für den Monat August 2017 gibt, dort aber keine Daten enthalten waren. Die wurden natürlich auch erst gar nicht importiert.

August 2017 ist das Stichwort. Laden Sie bitte diese Datei und kopieren die 4 entpackten Excel-Dateien in das noch leere Verzeichnis C:\Daten\201708. Um möglichst realitätsnah zu arbeiten schließen Sie nun ihre Excel Datei und speichern diese unter einem beliebigen Namen an einem beliebigen Ort. Falls noch andere Excel-Fenster geöffnet sind können bzw. sollten Sie diese auch schließen. Und wenn sie es ganz genau wissen wollen, dürfen Sie jetzt auch Windows neu starten, um eine garantiert „saubere“ Ausgangsposition zu haben; ich versichere Ihnen, das ist aber nicht wirklich erforderlich. Öffnen Sie nun die zuletzt gespeicherte Excel-Datei mit den importierten Werten. Schauen Sie nun gerne einmal nach, welche Tage in der Liste enthalten sind.

Überrascht? Es ist noch der alte Stand, das letzte Datum ist der 31. Juli 2017 in Zeile 428. Auch wenn es zu Beginn vielleicht etwas irritiert, das ist gewollt und auch gut so. Denn es ist durchaus möglich, dass sie am Vortag ihre Arbeit unterbrochen haben und heute auf dem derzeitigen Stand weiterarbeiten wollen. Da wäre es ziemlich misslich, wenn ohne ihr Zutun eine neue Datenlage vorhanden wäre. Sie bestimmen, wann die Daten auf den neuesten Stand gebracht werden sollen. Dazu muss der Cursor irgendwo in den Daten stehen; markieren Sie im Menü-Register den Punkt Abfrage und in der Gruppe Laden ein Klick auf Aktualisieren. Schon sind die Daten auf dem neuesten Stand. Und selbst wenn im alten Datenbestand Änderungen vorgenommen worden sind (was eigentlich nicht sein dürfte) wird sich das sofort in dieser Tabelle widerspiegeln. – Hinweis: Nehmen Sie dieses Verhalten bitte nicht als unveränderbar hin. Zu oft wird in Power Query dieses oder jenes Verhalten (ohne Ankündigung) geändert…

Dennoch gilt: Genau das ist der riesige Vorteil von Power Query. Ein Mal etwas Aufwand und anschließend die Daten täglich mit einem Mausklick aktualisieren, ohne kopieren zu müssen oder die neuen Daten zu öffnen. Apropos kopieren: Sie haben die Daten eben von Hand kopiert, damit auch aus dem Monat August Files eingelesen werden können. Wie anfangs beschrieben werden diese Dateien ja automatisch von einem System in das entsprechende Verzeichnis geschrieben und sie brauchen sich darum nicht zu kümmern. – Im Normalfall werden sie nun die Excel Datei bearbeiten und wieder unter dem gleichen Namen abspeichern, denn sie können ja durch filtern wir einen älteren Stand der Dinge simulieren.

Ich hoffe, dass sie aus diesem Skript bzw. auch dem Video den Nutzen ziehen können, den sie sich erhofft haben.

Dieser Beitrag wurde unter Daten zusammenführen, Datum & Zeit, File-Handling, Power Query, PQ-Formeln (Sprache M) abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Schreibe einen Kommentar