Auftragsdaten werden in einem Ordner gespeichert. Jeden Monat kommt eine Datei hinzu. Mit Power Query kann man leicht alle Dateien eines Ordners lesen und in eine Tabelle umwandeln - wir zeigen, wie das geht.

Das lästige Dateisystem

Natürlich wäre es schön, wenn wir alle Daten aus einer Datenbank oder über eine andere direkte Schnittstelle auf das jeweilige System beziehen könnten.

Aber: Realität trifft Theorie.

Es werden nur Export-Dateien zur Verfügung gestellt. Jeden Monat. Und wenn man die Daten einlesen möchte, muss man diese manuell zusammenfassen. Und wenn man die Dateien mit Excel bearbeitet, dann wurden nebenbei noch Datumsformate erkannt, wo keine waren oder Punkt mit Komma als Dezimaltrenner vertauscht.

Kurz: Es schleichen sich viele Fehler ein. Und wenn dann die Dateien zu groß werden, ist Excel ohnehin am Ende.

Schlüsseln wir das mal auf...

Wir haben im aktuellen Jahr drei Dateien mit Aufträgen: Januar, Februar und März.

  • Wir öffnen die Januar Datei
  • Wir öffnen die Februar Datei
  • Wir kopieren alle Daten der Februar-Datei
  • Wir fügen die Daten der Februar Datei in die Januar Datei ein
  • Wir merken, dass wir die Kopfzeile aus der Februar-Datei mitkopiert haben. Also: suchen, löschen
  • Wir speichern die Januar Datei besser unter einem anderen Namen

Dann öffnen wir die März Datei.

Eigentlich läuft es etwas anders

  • es sind 24 Dateien
  • nach der fünften Datei klingelt das Telefon
  • haben wir den März schon eingefügt?
  • wir sortieren nach Datum, um den letzten Eintrag herauszufinden - das dauert ewig
  • wir öffnen die nächste Datei - alles dauert immer länger
  • das Telefon kingelt....

Was eigentlich getan werden muss

  • Die Daten der Dateien müssen ausgelesen werden
  • Von einer Datei, beispielsweise einfach der ersten Datei im Verzeichnis, wird die erste Zeile als Kopfzeile verwendet
  • Die nächste Datei wird eingelesen - die erste Zeile wird ignoriert
  • Die nächste Datei wird eingelesen - die erste Zeile wird ignoriert - usw.

Folder.Files()

In Power Query (Excel und Power BI) legen wir eine neue Abfrage an. Typ "Ordner".

Neue Abfrage vom Typ Ordner in Power Query wählen

Als nächstes: Eingabe des Ordners, aus dem die Dateien gelesen werden sollen (das macht Power Query übrigens rekursiv - also inklusive Unterordner).

Ordner auswählen

Power Query liest die Dateien und zeigt diese an. Die Dateien kann man direkt einlesen und kombinieren (Daten kombinieren und transformieren). Wir machen das aber Schritt für Schritt.

Kombinieren und Transformieren

Wir wählen Daten transformieren und bekommen eine Liste mit den Dateien aus dem Ordner.

Abfrageergebnis: Alle Dateien des Ordners

Filtern nicht vergessen!

Power Query lädt alle Dateien aus dem Ordner und aus Unterordnern. Auch wenn dort fachlich völlig verschiedenen Dateien liegen. Im Beispiel liegen hier Textdateien und eine Excel-Datei. Die Excel-Datei möchten wir nicht kombinieren. Daher filtern wir die korrekten Dateien, indem wir einen Filter auf die Spalte Extension anlegen (nur .txt).

Kombinieren

Diese Funktion ist eine wirklich mächtige Funkion in Power Query. Für jede Datei erhalten wir in der oberen Abfrage eine Zeile. In der ersten Spalte finden wir den Content der Datei. Quasi der binäre Inhalt. Über der Spalte finden wir einen Doppelpfeil nach unten.

Power Query Funktion Combine im Spaltekopf

Wenn wir auf diese Schaltfläche klicken, versucht Power Query herauszufinden, um was für einen Dateityp es sich handelt. In meinem Fall um CSV-Dateien.

Combine Dialog in Power Query

Hier können wir oben links festlegen, aus welcher Datei Power Query die Spaltennamen und die Datentypen ableiten soll. Und dann können wir noch sagen, welche Zeichenkodierung verwendet werden soll und welches Trennzeichen. Handelt es sich um Excel-Dateien müssten wir an dieser Stelle mitteilen, welches Arbeitsblatt oder welche Tabelle wir einlesen möchten (diese muss dann in jeder anderen Datei auch vorhanden sein). Mit OK werden die Daten geladen.

Seltsame Artefakte in den Abfragen

Plötzlich finden wir seltsame Artefakte in den Abfragen.

Seltsame Artefakte nach Anlage der Abfrage

Hier hat Power Query alle Eingaben, die wir im Dialog angegeben haben "versteckt!. Also: welche Datei beinhaltet die Kopfdaten, welchen Zeichensatz möchten wir verwenden. Das heißt, diese Parameter und Funktionen dürfen wir nicht löschen. Sie sind Teil unserer Abfrage Aus Ordner.

Hinweis: Alles was in diesen generierten Funktionen stattfindet, kann man auch manuell selbst anlegen. Das führt nur für den Moment zu weit. Die generierten Funktionen sind gut. Nutzen Sie diese.

Und nächsten Monat?

Irgendwann, beispielsweise im nächsten Monat kommen Dateien hinzu. Die Abfrage müssen wir nicht ändern. In Power BI (oder Excel Power Pivot) führen wir einfach eine Daten-Aktualisierung durch. Es werden alle Dateien gelesen - also auch die Neue.

Noch ein abschließender Hinweis

Bei sehr vielen Dateien werden diese Abfragen langsam. Klar: Auch Power Query muss die Dateien Stück für Stück Öffnen und Laden. Wenn die Dateien dann noch auf einem Netzlaufwerk liegen und Sie sind am besten noch im HomeOffice und via VPN mit Ihrer Firma verbunden - dann zieht sich das.

Abhilfe schafft ein Zwischenformat. Sie lesen die Dateien mit derselben Abfrage in Excel ein und importieren die Ergebnisse in das Excel-Arbeitsblatt. Insbesondere bei Altdaten ist das prima, weil die ändern sich nicht mehr. Und dann verwenden Sie neben den neuen Dateien zusätzlich das konsolidierte Format.

Oder (eigentlich Und/Oder): Nutzen Sie doch auch OneDrive for Business