Aufrufe: 9910
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.
Willst du Power BI lernen?
Auf unseren neuen Seite Power BI Coach haben wir einen Onlinekurs zusammengestellt.
Dateien kombinieren – Schnelldurchlauf
Für diejenigen unter Euch, die nicht so auf viel Text stehen, habe ich das ganze Tutorial noch als Animation bereitgestellt.
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.
Schritt 2 – Importdateien suchen
Danach öffnet sich nachstehender Dialog. Wir klicken auf „Durchsuchen“…..
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.
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.
Damit wir nun die gewünschten Dateien weiter bearbeiten können, klicken wir auf die beiden Pfeile neben „Content“…
und erhalten eine Fehlermeldung.
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.
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.
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.
Ihr seht, dass nun sämtliche Spalten vorhanden sind. Wir entfernen noch alle nicht benötigten Spalten…
indem wir auf “Spalten entfernen” und “Andere Spalten entfernen” klicken.
Wir erhalten eine Tabelle mit allen Daten aus den beiden importieren Excel Dateien respektive deren Arbeitsblättern!
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“.
Im nun offenen Dialog noch auf „Erste Zeile als Überschriften verwenden“
Und wir haben folgendes Resultat:
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:
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:
Das Resultat in Excel:
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:
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.
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.
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!
