Dateien kombinieren

Dateien kombinieren geht ganz einfach mit Power Query. Dies hilft die Fehleranfälligkeit von Datenimporten zu minimieren und entlastet uns Monat für Monat von Routinearbeiten. In diesem Beitrag zeige ich, wie Ihr dieses Feature für Euch nutzen könnt. Prinzipiell kann diese Lösung sowohl in Excel als auch im Power BI Desktop angewendet werden.

Dateien kombinieren – Schnelldurchlauf

Für diejenigen unter Euch, die nicht so auf viel Text stehen, habe ich das ganze Tutorial noch als Animation bereitgestellt.

Dateien kombinieren

Und monatlich grüsst der Datenimport…

Die meisten Controller schieben zu Beginn des Monats Überstunden, um all die zugesandten Dateien zu konsolidieren, zu überprüfen und anzupassen. Dateien kombinieren, Monat für Monat, ist ein mühsamer, ermüdender und vor allem fehleranfälliger Prozess!

Jeder hat schon davon geträumt, ein einfaches Importtool zu haben, welches ihm diese Arbeit abnimmt. Power Query setzt genau hier an.

Import aus Ordner – Schritt für Schritt

Im Idealfall sind die zu bearbeitenden Excel Dateien gleich aufgebaut. Das heisst, dass jede Datei über die gleichen Spaltenüberschriften und Datenelemente verfügt. Hier kann nun statt mit endlosen Sverweisen oder Kombination via Pivot Tabellen oder – noch schlimmer und vor allem fehleranfälliger – manuellem Kopieren und Einfügen, ganz einfach vorgegangen werden.

Power Query hilft uns, die gleichartigen Dateien zu importieren, gleich zu formatieren und dann entweder in Power Pivot oder in eine Excel Tabelle zu importieren. Wir schauen uns den Fall des Datenimports in eine Excel Tabelle an.

Schritt 1

Wir wählen den Ritter „Power Query“ aus, danach Punkt „Aus Datei“. Es öffnet sich ein Dropdown-Fenster, in welchem wir auf „Aus Ordner“ klicken.

Quelle aus Ordner

Schritt 2 – Importdateien suchen

Danach öffnet sich nachstehender Dialog. Wir klicken auf „Durchsuchen“…..

image

und dann auf den gewünschten Ordner mit den enthaltenen Excel Dateien. Ohne vertiefte Kenntnisse der Power Query Skriptsprache „M“ ist es zwingend, dass alle zu importierenden Excel Dateien im gleichen Ordner liegen.

Power Query - Ordner suchen

Schritt 3 – Power Query Schritte

Nachdem wir den Importordner ausgewählt haben, wechselt Excel automatisch in die Benutzeroberfläche von Power Query und folgende Ansicht erscheint.

vorhandene Dateien

Damit wir nun die gewünschten Dateien weiter bearbeiten können, klicken wir auf die beiden Pfeile neben „Content“…

in Editor geladen

und erhalten eine Fehlermeldung.

Fehlermeldung xlsx kombinieren

Im Gegensatz zu csv Dateien kann Power Query die Exceldateien nicht auf diese Art und Weise kombinieren. Aber es gibt eine einfache Lösung, indem wir zuerst eine Spalte hinzufügen.

xlsx kombinieren via definierte Spalte - gross

Als Nächstes erweitern wir die neue Spalte. Hier ist lediglich der Eintrag “Data” zu wählen. Achtet darauf, dass ihr den Haken bei “Ursprüngliche Spaltennamen als Präfix verwenden” deaktiviert, da ansonsten der Spaltenname statt “Data” “Benutzerdefiniert.Data” heissen würde.

Data laden

Die erhaltene Spalte “Data” erweitern wir wie oben, wählen aber alle Einträge, da wir noch nicht wissen, welche Spalten die gesuchten Daten enthalten.

Data Spalte erweitern

Ihr seht, dass nun sämtliche Spalten vorhanden sind. Wir entfernen noch alle nicht benötigten Spalten…benötigte Spalten wählen

indem wir auf “Spalten entfernen” und “Andere Spalten entfernen” klicken.
Andere Spalten entfernen

Wir erhalten eine Tabelle mit allen Daten aus den beiden importieren Excel Dateien respektive deren Arbeitsblättern!
Benötigte Spalten

Ist das magisch?? Nein, das ist Power Query!

Schritt 4 – Daten bearbeiten

Sicher habt Ihr auch bemerkt, dass die übrig gebliebenen Spalten nicht die Originalüberschriften enthalten. Das wollen wir nun ändern.
Dazu wählt ihr im Ritter „Transformieren“ und danach „Erste Zeile als Überschriften verwenden“.

image

Im nun offenen Dialog noch auf „Erste Zeile als Überschriften verwenden“

image

Und wir haben folgendes Resultat:

image

Nun können wir für jede Spalte, wo nötig, noch den Datentyp anpassen.

Wir klicken die Spalte „Produkt“ an, wechseln zum Ritter „Start“ und zu „Datentyp:

image

Und wählen dort den gewünschten Datentyp, hier „Ganze Zahl“, aus. Das gleiche wiederholen wir mit den anderen Spalten;

  • Datum = Datum 😉
  • Umsatz = Dezimalzahl
  • Anzahl = Ganze Zahl

Und schon haben wir eine sauber formatierte Tabelle, die wir nun in eine Excel Tabelle importieren können.

Schritt 5 – Daten in Excel laden

Wir gehen über „Start“, „Schliessen & laden“ und zu Letzt wählen wir „Schliessen & laden in“.

Da wir in einem neuen Excel Blatt eine Tabelle mit den importierten Daten haben wollen, klicken auf die entsprechenden Buttons:

image

Das Resultat in Excel:

image

Die gewünschten Daten stehen kombiniert zur Verfügung, deshalb können die weiteren ordentlichen Arbeiten am Reporting weitergehen. Das Resultat sieht als Pivot Tabelle wie folgt aus:

image

Schritt 6 – Daten aktualisieren

Sobald der Datenimport wieder aktualisiert werden muss, aktiviert Ihr in Excel wiederum den Menüpunkt „Power Query“. Am rechten Rand von Excel erscheint eine Liste aller erstellten Queries.

image

Ihr wählt die gewünschte Abfrage aus und führt einen Rechtsklick aus. Danach erscheint ein Popup-Fenster, in welchem Ihr „Aktualisieren“ wählt.

image

Und schon führt Power Query den Import nochmals durch.

Vielleicht habt Ihr auch festgestellt, dass unter Abfrage die Anzahl der importierten Zeilen steht. Diese Zahl ist für mich immer ein wichtiger Hinweis darauf, ob wirklich so viele Zeilen wie erwartet importiert wurden.

Fazit

Das Tolle an dieser Kombinationsmöglichkeit mittels Power Query ist, dass der einmal erstellte Report immer wieder aktualisiert werden kann. Das Power Query Skript läuft Befehl für Befehl durch und wiederholt die erfassten Schritte.

Schöne Zeit für Data Worker wie zum Beispiel Controller:
In Zukunft lässt Ihr Power Query für Euch arbeiten und alle gewünschten Excel Dateien werden auf Knopfdruck zuverlässig importiert. Monat für Monat könnt Ihr so Dateien kombinieren!

Habt Ihr diese Funktion schon gekannt? Wendet Ihr sie regelmässig an? Berichtet uns von Euren Erfahrungen.

Mehr Artikel über Power Query findet Ihr im Power Query Online Buch.

Happy Querying!