- Mit Power Query können Sie mehrere Dateien aus einem lokalen Ordner oder einem Cloud-Ordner zusammenführen, sofern diese das gleiche Datenschema aufweisen.
- Die Verwendung einer Beispieldatei erleichtert das Anwenden von Massentransformationen, wie z. B. das Entfernen der ersten Zeilen oder das Ändern der Überschriften.
- Es gibt Einschränkungen, wenn die Spalten nicht exakt übereinstimmen, was eine manuelle Bereinigung oder die Verwendung von VBA-Skripten für komplexe Fälle erfordert.
Stellen Sie sich vor, Sie haben unzählige Monats- oder Wochenberichte, die über verschiedene Ordner verstreut sind. Anstatt Ihren Morgen mit dem hektischen Kopieren und Einfügen von Daten zu vergeuden, könnten Sie alles mit nur wenigen Klicks in einer einzigen Excel-Tabelle zusammenführen. Genau hier kommt Power Query ins Spiel – ein Tool, das für jeden, der mit Daten arbeitet, unverzichtbar ist und es ermöglicht, Dateien mit derselben Struktur mühelos zu konsolidieren.
Das Geniale an diesem System ist, dass es Sie nicht auf einen lokalen Ordner auf Ihrem PC beschränkt; Sie können Daten von SharePoint-Websites, Azure Data Lake oder Azure Blobs abrufen . Wichtig für einen reibungslosen Ablauf ist, dass die Dateien dasselbe Schema haben, d. h. die Spalten übereinstimmen. Power Query ist jedoch intelligent genug, um die Daten zu bereinigen, falls sie überflüssige oder fehlerhafte Zeilen enthalten.
Der schrittweise Kombinationsprozess
Wenn Sie eine Datenquelle verbinden, sehen Sie zunächst die Dateisystemansicht. Die wichtigste Spalte ist hier „ Inhalt“, die die Binärdarstellung jedes Dokuments enthält. Wenn Sie auf einen dieser Binärwerte klicken, versucht das Programm zu ermitteln, ob es sich um eine CSV-, JSON- oder Excel-Datei handelt, um sie zu öffnen. Um alle Dateien zusammenzuführen, haben Sie zwei Möglichkeiten: Klicken Sie direkt auf die Schaltfläche „Zusammenführen“ oder, falls Sie genauer vorgehen möchten, wählen Sie „ Daten transformieren“, um die Dateiliste zu filtern (z. B. um bestimmte Ordner zu ignorieren), bevor Sie mit dem Zusammenführen fortfahren.
Sobald Sie das Dialogfeld „Zusammenführen“ öffnen, analysiert Power Query die Struktur anhand einer Beispieldatei . Standardmäßig wird die erste Datei in der Liste ausgewählt. Sie können dies jedoch ändern, falls eine andere Datei die Daten besser repräsentiert. Hier können Sie die Option zum Überspringen fehlerhafter Dateien aktivieren . Dies ist besonders hilfreich, wenn ein Bericht beschädigt oder leer ist, da so der gesamte Prozess nicht abgebrochen wird.
Ausgabemanagement und Datenbereinigung
Nach der Annahme des Zusammenführungsvorgangs führt das System im Hintergrund einige technische Prozesse durch: Es erstellt eine Beispielabfrage und eine parametrisierte Funktion. Das bedeutet, dass alle Änderungen, die Sie an der Beispieldatei vornehmen, automatisch auf alle anderen Dateien im Ordner übernommen werden. Wenn Ihre CSV-Dateien beispielsweise vier unnötige Kopfzeilen vor den eigentlichen Daten enthalten, können Sie in der Transformationsabfrage die Option zum Entfernen der ersten Zeilen verwenden , um diese zu bereinigen.
Nach dem Bereinigen der Zeilen ist es wichtig, die Funktion „ Erste Zeile als Überschriften verwenden“ anzuwenden . Beachten Sie, dass Power Query dabei in der Regel eine automatische Datentypkonvertierung durchführt. Sollten Sie nach der Rückkehr zur Hauptabfrage eine Fehlermeldung erhalten, dass die Spalte „Spalte1“ nicht gefunden wurde, ist das kein Grund zur Sorge. Dies liegt daran, dass Sie die Spalten im Beispiel umbenannt haben und der nachfolgende Schritt die alten Namen nicht mehr erkennt. Löschen Sie einfach den fehlerhaften Schritt im Bereich „Angewendete Schritte“, und alles funktioniert wieder normal.
Gemeinsame Herausforderungen und fortschrittliche Alternativen
Manchmal ist das Leben nicht so einfach, und wir stoßen auf Dateien, die nicht identisch sind. Wenn Sie beispielsweise ServiceNow-Berichte haben, in denen eine Überschrift „resolved_on“ und die andere „closed_on“ heißt, kann Power Query Probleme verursachen, da es eine einheitliche Struktur erfordert . Stimmt die Beispieldatei nicht exakt mit den übrigen Dateien überein, kann das Tool die Informationen aus den abweichenden Dateien auslassen oder Importfehler ausgeben.
Für Fälle, in denen die Struktur völlig chaotisch ist oder das Datenvolumen enorm ist (mehr als eine Million Excel-Zeilen), gibt es Alternativen. Manche Anwender entscheiden sich für VBA-Skripte mit CSV-Schnittstellen . Diese ermöglichen das Zusammenführen von Dateien ohne doppelte Kopfzeilen, unabhängig davon, ob das Feld- oder Datensatztrennzeichen von Datei zu Datei variiert. Die Windows-Shell ist zwar extrem schnell, stößt aber oft an ihre Grenzen bei eingebetteten Zeilenumbrüchen. Eine robuste VBA-Lösung hingegen überlastet den Arbeitsspeicher nicht und kann problemlos Millionen von Datensätzen verarbeiten.
Um den Vorgang abzuschließen, weisen Sie nach dem Zusammenführen der Daten in Power Query jeder Spalte den korrekten Datentyp zu (z. B. Datum für Datumsangaben, Währung für Einkommen usw.), bevor Sie die endgültige Tabelle laden. Überprüfen Sie die Korrektheit, indem Sie die Spalte „Quellname“ filtern und so sicherstellen, dass alle Dateien im Ordner erfolgreich verarbeitet wurden.
Die Möglichkeit, das Zusammenführen mehrerer CSV-Dateien zu automatisieren, ermöglicht es Ihnen, von mühsamer manueller Arbeit zu einem professionellen Workflow überzugehen, sei es durch die Nutzung der Flexibilität von Power Query zur Datenbereinigung oder durch den Einsatz von VBA, wenn Strukturen inkompatibel sind oder die Datenmengen die Grenzen von Tabellenkalkulationen überschreiten.