Im letzten Artikel habe ich gezeigt, wie man mit Hilfe von PowerQuery einen kompletten Verzeichnisbaum in Excel einliest und somit mit minimalem Aufwand eine Übersicht über alle enthaltenen Dateien erhält, nebst Erstellungsdatum und Dateigröße.
Jetzt gehen wir einen Schritt weiter und lesen die Inhalte aller Dateien eines Verzeichnisses in einem Rutsch in eine einzige Excel-Tabelle ein.
Wie das geht und welche Voraussetzungen dabei zu beachten sind, erfährst du im folgenden Beitrag.
Solltest du PowerQuery noch nicht kennen, empfehle ich dir, zuerst diese beiden Blog-Artikel zu lesen:
Einführung in Power-Query – Teil 1
Einführung in Power-Query – Teil 2
Und wenn du einen fundierten und angeleiteten Einstieg möchtest, dann solltest du dir meinen Online-Kurs Daten importieren und aufbereiten mit Power Query ansehen.
Die Ausgangslage
Ein nicht ganz unübliches Szenario im Leben eines Datenanalysten oder Controllers:
Es liegt eine Vielzahl von gleichartig aufgebauten Dateien vor, deren Inhalte für weitere Analysen alle in einer großen Excel-Tabelle zusammengefasst werden sollen.
Das können z.B. regelmäßige Exporte aus einem ERP-System wie SAP sein, oder die monatlichen Umsatzreports aus den verschiedenen Niederlassungen. Die Anzahl der Dateien schwankt, ebenso die Anzahl der Datensätze innerhalb der einzelnen Dateien. Lediglich der Aufbau, also die Spaltenanordnung, ist vorgegeben und immer identisch.
Der motivierte Controller hat nun jeden Monat (oder gar jede Woche) das Vergnügen, alle Dateien einzeln zu öffnen, die Inhalte in seine vorbereitete Auswertungstabelle zu kopieren und die Quelldateien danach wieder zu schließen. Eine sehr spannende und zufriedenstellende Tätigkeit – insbesondere bei sehr vielen Quelldateien – bei der Fehler auch völlig ausgeschlossen sind…
Und die Erde ist eine Scheibe.
Das geht sicherlich besser, oder?
Der schlaue Controller…
…nutzt natürlich – PowerQuery!
Zu Demonstrationszwecken habe ich sechs Dateien mit Auftragsinformationen erstellt und in einem gemeinsamen Verzeichnis abgespeichert:
Liste mit den Quelldateien
Natürlich könnten das auch viel mehr Dateien sein, für das heutige Beispiel sollte das aber genügen. Alle sechs Dateien haben den gleichen Aufbau, lediglich Inhalt und Anzahl der enthaltenen Datensätze sind unterschiedlich:
Aufbau der Quelldateien
In den nächsten Schritten werden wir diese Dateien mit Hilfe von PowerQuery importieren.
In der Registerkarte „Daten“ klickst du auf die Schaltfläche „Daten abrufen“. Im angebotenen Menü wählst du „Aus Datei“ und dann „Aus Ordner“:
Eine neue Abfrage erstellen
Im nächsten Fenster wird das Quellverzeichnis benötigt, in dem die Dateien gespeichert sind. Hier kannst du dich über die „Durchsuchen“-Schaltfläche zum gewünschten Ort durcharbeiten:
Quellordner angeben
Ein Klick auf die OK-Schaltfläche bringt dich zu einer Auflistung aller Dateien in diesem Verzeichnis. Wenn du den letzten Artikel zum Einlesen einer Ordnerstruktur gelesen hast, wird dir das sehr bekannt vorkommen. Der wesentliche Unterschied liegt darin, dass wir jetzt mit der „Kombinieren“-Schaltfläche weitermachen:
Vorschau auf das Quellverzeichnis
Hier gibt es drei Möglichkeiten:
Kombinieren und bearbeiten
Damit wird der Abfrage-Editor geöffnet und die Daten werden erst dort eingeladen. Diese Option ist dann gefragt, wenn du vor dem Import in eine Excel-Tabelle noch weitere Transformationen an den Daten vornehmen möchtest.
Kombinieren und laden
Damit erfolgt der Import ohne den Umweg über den Abfrage-Editor direkt in Excel und die Daten werden alle in ein neues Arbeitsblatt der aktuell geöffneten Arbeitsmappe geladen.
Kombinieren und laden in…
Auch hier wird der Import direkt nach Excel durchgeführt, allerdings kann man noch angeben, wohin genau die Daten geladen werden sollen. Das ist ganz hilfreich, wenn du schon eine entsprechende Tabelle vorbereitet hast.
Der Einfachheit halber habe ich mich für die zweite Option entschieden (Kombinieren und laden). Im nächsten Fenster
möchte Excel wissen, welche Daten genau importiert werden sollen. Ich übernehme im ersten Dropdown-Feld den Eintrag „Erste Datei“. Damit wird im unteren Teil des Fensters auf der linken Seite eine Liste der enthaltenen Arbeitsblätter angezeigt. Da sich die zu importierenden Daten in Tabelle1 befinden, markiere ich diesen Eintrag und bekomme rechts zur Kontrolle eine kleine Vorschau.
Vorschau auf die erste Datei
Ein Klick auf OK startet den Import. Je nach Anzahl und Umfang der Dateien kann dieser Vorgang unterschiedlich lange dauern. Da es sich in meinem Beispiel nur um sechs Dateien handelt, ist das Ergebnis nach wenigen Augenblicken zu sehen:
Der Import ist abgeschlossen
Excel hat ein neues Arbeitsblatt angelegt und darin eine neue formatierte Tabelle erstellt, die die Inhalte sämtlicher sechs Quelldateien enthält. Die zusätzlich erzeugte Spalte „Source.Name“ gibt Auskunft darüber, aus welcher Datei die jeweiligen Daten stammen.
Außerdem hat sich rechts der Arbeitsbereich mit den Abfragen und Verbindungen geöffnet. Im oberen Bereich werden ein paar Verbindungseinträge gezeigt, die wir hier nicht weiter beachten müssen (dahinter verbirgt sich die Logik, die beim Import umgesetzt wurde).
Interessant für uns ist der letzte Eintrag in der Liste, der bei mir „Import-1“ heißt (nach dem Namen des Quellverzeichnisses). Hier sieht man auch, wie viele Datensätze insgesamt geladen wurden.
Eine kleine Anpassung
Da wir den Import direkt in Excel durchgeführt haben, wurden die Dateien so geladen, wie sie im Quellverzeichnis stehen. Das heißt, die Sortierung erfolgt nach dem Dateinamen (Feld „Source.Name“).
Sortierung nach dem Dateinamen
Schöner wäre es jedoch gewesen, wenn die Liste gleich nach dem Datum sortiert worden wäre.
Dazu muss nur die Abfrage ein wenig angepasst werden: Mit einem Doppelklick auf den Abfrageeintrag „Import-1“ wird der Abfrage-Editor gestartet.
Abfrage bearbeiten
Dort kann man über das Dropdown-Feld der Spalte „Datum“ die Liste aufsteigend sortieren lassen:
Sortieren der Datumsspalte
Über die Schaltfläche „Schließen & Laden“ wird die nun sortierte Liste zurück an Excel übergeben. Natürlich hätte man auch direkt die formatierte Tabelle in Excel sortieren können, ohne nochmal den Umweg über den Abfrage-Editor zu gehen. Der Vorteil in meiner Vorgehensweise liegt jedoch darin:
Jetzt ist die Sortierung fest in der Abfrage hinterlegt. Wenn du also beim nächsten Mal die Daten erneut einliest, kommen sie gleich nach Datum sortiert in Excel an.
Wie man die Daten nochmal einliest? Ein Rechtsklick auf die Abfrage und dann die Option „Aktualisieren“. Und schon wird die Abfrage erneut ausgeführt und alle Dateien werden erneut importiert.
Daten erneut einlesen
Vielleicht hast du ja ein paar andere Anwendungsfälle für solch einen Massenimport auf Knopfdruck. Dann lass es uns unten in den Kommentaren wissen.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hi,
danke erst mal für die Powerquery-Tutorials. Kleine Frage:
Wird beim Aktualisieren der Abfrage das komplette Verzeichnis neu eingelesen (also auch etwaige neue Dateien), oder nur solche Dateien, die bereits eingelesen (und vlt. geändert wurden)?
Schöne Grüße
-Tim
Hi Tim,
beim Aktualisieren wird das komplette Verzeichnis neu eingelesen. Und damit auch neu hinzugekommene oder geänderte Dateien.
Dateien hingegen, die seit dem letzten Import aus dem Verzeichnis gelöscht wurden, verschwinden dann auch aus der importierten Excel-Tabelle.
Schöne Grüße,
Martin
Kann es sein, dass da in Excel 2016 einiges ein wenig anders funktioniert ? Ich finde z.B. keinen „Kombinieren-Button“.
Nach vielem Probieren habe ich es dann an ganz anderer Stelle im Abfrage-Editor über Content und dem Symbol „Binärdateien kombinieren“ geschafft, den Massenimport aus mehreren Dateien anzustoßen.
Wenn es dann endlich funktioniert, ist das ein tolles Feature, das wirklich Zeit sparen kann.
Es wäre aber hilfreich, wenn im Blog auf die Unterschiede zwischen dem PowerQuery als Addon und Excel 2016 eingegangen würde.
Beste Grüße, Stephan
Hallo Stephan,
danke für den Hinweis. Es ist natürlich immer gut möglich, dass manche Dinge in Excel 2016 etwas anders funktionieren. In diesem Fall dürfte es jedoch daran liegen, dass Dein PowerQuery-AddOn vermutlich nicht mehr ganz aktuell ist. Ich habe es gerade nochmal unter Excel 2010 probiert, und hier gibt es auch den „Kombinieren“-Button (meine Version: 2.48.4792.941). Microsoft aktualisiert das AddOn immer mal wieder, von daher lohnt es sich, ab und zu mal eine neue Version herunterzuladen (aktuelle Version: 2.49.4831.381 vom 22.09.2017)
Schöne Grüße,
Martin
Hallo Martin,
Excel 2016 blockiert die Installation des PowerQuery-Addon mit dem Hinweis, dass die Funktionen nun vollständig in Excel 2016 integriert sind. Ich hatte die Installation der o.g. Version tatsächlich schon versucht, aber ohne Erfolg. Das heißt, dass wir uns mit der im Handling zum Teil deutlich veränderten PowerQuery-Implementierung des Excel 2016 arrangieren müssen. Weiß nicht, ob man im Blog hier und da auf die Unterschiede eingehen kann. PowerQuery ist aber in jedem Fall ein geniales Werkzeug und der Blog ein super Hilfe für den Einstieg.
Beste Grüße, Stephan
Hallo Stephan,
dann lag hier wohl ein Missverständnis vor: Ich bin davon ausgegangen, dass Du NICHT mit Excel 2016 arbeitest, denn mein Blogartikel basiert auf Excel 2016. Und hier muss natürlich kein AddOn installiert werden, diese gilt nur für Excel 2013 und 2010. Möglicherweise gibt es dann noch einen Unterschied zwischen Excel 2016 Einzellizenz und Excel 2016 im Rahmen von Office 365. Letzteres wird von mir eingesetzt. Leider sind das Versionsthema und die damit verbundenen Unterschiede auch für mich nicht immer ganz zu durchschauen…
Schöne Grüße,
Martin
Hallo Martin,
das ist ein wirklich interessanter Fall, denn auch ich benutze das Excel im Rahmen einer Office365-Lizenz. Dennoch scheint es Unterschiede zu geben, wie man dieser Notiz entnehmen kann:
http://www.excel-ticker.de/aenderungen-an-den-funktionen-im-menueband-zum-abruf-externer-daten/
Meine Excel-Version ist 1701 (Build 7766.2099). Wie der Link beschreibt, gibt es zur Version 1703 (Build 7920.1000) eine Änderung in der Menüstruktur (und wohl auch in der Ausprägung bestimmter Funktionen). Das beantwortet die Frage, warum ich nicht alle im Blog beschriebenen Arbeitsschritte 1:1 nachvollziehen kann.
Da sieht man, wie aktuell die Weiterentwicklung von PowerQuery durch Microsoft verfolgt wird.
Grüße, Stephan
Hallo Stephan,
vielen Dank für den Hinweis und den Link dazu, das klingt nach einer guten Erklärung. Das ganze Thema läuft ja bei Microsoft unter „Power-BI“ und wird ganz offensichtlich massiv vorangetrieben.
Schöne Grüße,
Martin
good
Hallo Martin,
Der Blog zu MS Power Query is schonmal sehr hilfreich.
Allerdings habe ich eine Problemstellung, zu der ich noch keine Lösung gefunden habe.
Es geht um den Massenimport von 100+ Tabellenblättern, die immer nach dem gleichen Schema aufgebaut sind.
Jedoch liegen die Daten in der jewiligen Datei nicht in Tabellenform vor, sondern liegen „verstreut“ ab.
Man kann es sich so vorstellen, dass die Referenzdatei eine Art Projektsteckbrief ist.
Z.B. Der Projektname steht immer in Zelle C3, die Angebotsnummer in Zelle H14, der Produkttyp in Zelle C20 usw.
Leider kriege ich so keine geeingete Transformation hin, um die benötigten Informationen über Power Query als neue Tabelle mit den gewünschten Informationen als Spalten anzuzeigen.
Den Umweg in jedem Blatt ein zweites Arbeitsblatt anzulegen, dass die Information als Liste darstellt möchte ich vermeiden, da ich dann jede Datei erstmal von Hand anpassen muss.
Gruß
Richard
Hallo Richard,
wenn ich es richtig verstehe, möchtest Du aus jedem dieser 100+ Arbeitsblätter jeweils einige Informationen auslesen, die in jedem Blatt an der gleichen Stelle stehen (C3, H14, C20…). Und die gesammelten Infos sollen dann in einer separaten Tabelle aufgelistet werden? Offen gestanden fällt mir mit PowerQuery hierfür auch keine Lösung ein. Vielleicht hat ja ein anderer Leser eine Idee. Ich tippe jedoch eher auf eine VBA-Lösung.
Schöne Grüße,
Martin
Hallo Martin,
ich habe mittlerweile, auch Danke deiner Anleitungen hier, verstanden, dass Power Query dazu da ist mit Tabellen/Listen zu arbeiten. Damit habe ich einen recht simplen aber effizienten Workaround gefunden.
In meinem Arbeitblatt Vorlage habe ich eine neue 2 Spalten Tabelle angelegt und mit gewünschten Informationen verknüpft (Z.B. =C3, =H14, usw. Die Tabelle habe ich markiert und z.B. mit „Daten“ benannt.
PQ durchsucht nun das komplette Verzeichnis und Unterodner! nach dem Arbeitsblatt (heißt immer gleich z.B. xy.xls) und ließt den Parameter „Daten“ (also meine eigens definierte Tabelle) aus. Genial!
Gruß
Richard
Hallo Richard,
genau so soll es sein: Tipps aufgreifen, damit herumexperimentieren und tolle Lösungen finden!
Schöne Grüße,
Martin
Hallo Martin,
das habe ich mal mit einem dynamischen Bereich versucht, wie hier schon mal beschriebeb mit „verschieben“. Hat leider nicht geklappt. Beim Import wird dieser nicht erkannt bzw. Nicht angezeigt. Ein fester Import geht aber, warum auch immer. Gibt es evtl. Beim Import eine Möglichkeit leere Zeilen nicht zu importieren? Dann könnt ich einen größeren Bereich definieren und dann die leerzeilen rauswerfen.
Oder wie könnte es gehen? Freue mich auf Vorschläge.
Gruß Tino
Hallo Tino,
es gibt sicherlich verschiedene Ansätze. Wenn du zum Beispiel mit formatieren Tabellen arbeitest (was meistens zu empfehlen ist), hast du überhaupt kein Problem mit dynamischen Bereichen. Denn die Tabelle wächst automatisch mit und damit auch der Bereich.
Ansonsten kannst du natürlich erst mal alles in Power Query importieren und dort anschließend Leerzeilen einfach in Power Query ausfiltern. Auch das ist ein durchaus übliches Vorgehen.
Schöne Grüße,
Martin
Hallo Martin,
dank deiner Seite habe ich angefangen mit Power Query zu experimentieren.
Dieses Tool bringt mich sowas von voran!
Allerdings habe ich eine Sache noch nicht rausgefunden: Ich möchte meine Daten erst ab einer bestimmten Zeile auswerten, allerdings ist das nicht immer die selbe Zeile, sondern startet ab einem bestimmten Begriff und soll bis zur ersten Leerzeile gehen.
Gibt es da eine Lösung?
Danke und viele Grüße
Hallo Kathrin,
das freut mich zu hören, dass ich Dein Interesse geweckt habe!
Am einfachsten könntest Du das Problem lösen, wenn die relevanten Daten als Tabelle formatiert wären (Start | Als Tabelle formatieren).
Dann spielt es nämlich überhaupt keine Rolle, wo sich diese Tabelle auf dem Arbeitsblatt genau befindet.
Ansonsten dürfte es eher kniffelig werden…
Schöne Grüße,
Martin
Hi,
zunächst Danke für die Tutorials.
Mir stellt sich gerad eine Frage:
Ich würde gerne mehrere Abfragen quasi „zeitgleich“ von Excel bearbeiten lassen.
Expliziter:
Ich habe eine Ordner mit mehreren Unterordnern, in diesen Unterordnern befinden sich ebenfalls mehrerer Exceldateien.
Mit „Neue Abfrage – Aus Ordner“ kann mir Excel ein Tabellenblatt erstellen, wo alle Einträge unter einander gefasst werden.
Besitzt Excel die Funktion, dass für jeden Unterordner ein neues Tabellenblatt erstellt wird, sodass man nicht immer manuell eine neue Abfrage auswählen muss.
Danke im Voraus und BG aus dem Norden
Hi Tim2,
du könntest für jeden Unterordner einmalig eine eigene Abfrage erstellen, die dann in ein eigenes Arbeitsblatt geladen wird. Dazu brauchst Du nur die erste Abfrage kopieren und lediglich den Namen des Unterordners anpassen.
Schöne Grüße,
Martin
Hi Martin,
genauso gehe ich gerade vor.
Habe jedoch Unmengen von Daten vorliegen, sodass selbst dies (mit nachfolgenden Schritten) schon Tage dauert.
Aber wenn selbst du dort keinen Trick kennst, dann geht es wohl nicht anders 😉
Danke für deine Antwort und liebe Grüße,
Tim
Hallo Tim2,
vielleicht hilft Dir der Workshop von Andreas Thehos weiter: „Power Query – Parameter steuern Abfragen“. Ich verwende diesen Ansatz und vermeide es dadurch, in Power Query Abfragen feste Quellen „einzubrennen“. Ich habe dies für Dein Anliegen nicht ausprobiert, aber vielleicht ist es ja eine Anregung.
Viele Grüße
Andrea
Hallo genau zu diesem Thema hab ich ein Problem.
Ich habe Ausgangsdaten die in einer Mastertabelle gespeichert sind, dort lese ich mit Power Query die für mich wichtigen Daten in eine neu tabelle aus. Soweit so gut alles Spitze leider muss ich hier noch manuell mehrere Spalten einfügen, welche ich manuell befülle.
auch kein Problem, jetzt kommt aber der Knackpunkt da die Masterdaten sich ständig ändern spricht es kommen Zeilen hinzu oder fallen raus, wenn ich jetzt die Daten erneut über Power Query importiere werden alle manuellen daten in meiner Tabelle komplett zuerstört sprich es meine zusätzlichen Spalten sind nicht mit den ausgelesenen daten verknüpft und werden so mit nicht an die ausgelesenen daten angepassst. Ich hoffe ich habe mich verständlich ausgedrückt und sie können mir helfen.
mfG Th.Thiede
Hallo Herr Thiede,
das ist in der Tat ein Problem und dürfte etwas kniffelig werden, denn es besteht ja zwischen den Original-Quelldaten und den manuell hinzugefügten Daten keinerlei Verbindung.
Ich würde vermutlich versuchen, vor der neuen Aktualisierung eine statische Kopie der bereits angereicherten Tabelle zu erstellen, also ohne Datenverbindung. Und anschließend die statische Tabelle und die neu eingelesenen Quelldaten über einen Join wieder zusammenzuführen. Aber eine vollautomatische „Auf-einen-Knopfdruck-Lösung“ sehe ich spontan nicht.
Schöne Grüße,
Martin
Normalerweise würde ich auf diesen Artikel verlinken:
https://exceleratorbi.com.au/self-referencing-tables-power-query/
Allerdings gibt es da gerade eine Meldung:
Error 526
Ray ID: 4adc0a20291dc4e2 • 2019-02-23 19:18:40 UTC
Invalid SSL certificate
Hallo XLarium,
vielen Dank für den hilfreichen Link mit dieser cleveren Lösung!
Bei mir kam die Fehlermeldung übrigens nicht; vermutlich war das nur irgendein temporäres Problem.
Schöne Grüßen
Martin
Hallo XLarium,
vielen Dank für das Teilen dieses Links! Ich hatte dasselbe Problem wie Herr Thiede und war lange auf der Suche nach der Lösung. Jetzt klappt alles wunderbar und die Tabellen aktualisieren sich schön geordnet!
Danke und viele Grüße!
Hallo,
wie kann ich bei dieser Funktion die Überschriften der Tabellen definieren?
Meine Exeldatei ist so komplex, dass Query sie nicht erkennt.
Hallo Mario,
das hängt vom konkreten Aufbau der eingelesenen Daten ab. Wenn beispielsweise oberhalb der Tabellen nicht benötigte Zeilen sind, können die im Power Query-Editor erst entfernt werden (Zeilen entfernen | Erste Zeilen entfernen) und dann kann die verbliebene Überschriftenzeile übernommen werden (Erste Zeile als Überschriften verwenden).
Grundsätzlich lässt sich die Überschriftenzeile aber auch manuell im Editor anpassen: Doppelklick auf die Überschrift und dann die gewünschte Bezeichnung eingeben.
Schöne Grüße,
Martin
Ich hätte da mal eine Frage, ist es denn möglich mit der Abfrage funktion aus drei gleich aufgebaute Excel Tabellen die Inhalten abzurufen und sie zusammenzuführen? Nehmen wir man 3 Leute bearbeiten eine eigene Liste mit Geräten und da soll unter anderem Bestände aufgeführt werden. Hier die Bedingungen, wenn einer den Bestand für ein Gerät von 1 auf 3 ändert, soll dieser Wert abgerufen werden. Sowie wenn der Bestand durch den anderen von 3 auf 2 geändert wurde. ? Ist es damit möglich, wenn wie ?
Hallo,
mit Power Query lassen sich mit der oben beschriebenen Technik nur ganze Tabellen einlesen, nicht jedoch einzelne geänderte Werte. Das Problem ist aber eigentlich ein ganz anderes: Wenn ich es richtig verstehe, arbeiten unterschiedliche Leute mit jeweils einer eigenen Datei, die aber alle die gleichen Daten (hier Geräte und deren Bestände) enthalten. Das heißt, es könnten auch mehrere Leute beim gleichen Gerät den Bestand ändern, aber eben in ihrer Datei. Damit gibt es einen Konflikt, der weder durch Power Query noch durch eine andere Technik gelöst werden kann. Denn wie soll Power Query beim Zusammenführen der Daten entscheiden, was letztendlich die richtigen Bestände sind?
Wenn also wirklich mehrere Leute die gleichen Daten verwalten, sollte nicht mit separaten Dateien gearbeitet werden. Eine zentrale Datenbank wäre hier der bessere Ansatz. Oder, falls Office 365 zum Einsatz kommt, eine gemeinsam genutzte Datei, die auf OneDrive liegt und zur gleichzeitigen Bearbeitung freigegeben ist.
Schöne Grüße,
Martin
Moin, ich habe da eine Frage zu Power query. Ich mache eine Datenabfrage aus einem Ordner, wie ihr es auch hier beschrieben habt: https://www.tabellenexperte.de/massen-import-mit-powerquery/.
Jedoch dauert sehr lange, besteht eine Möglichkeit, dass ganze zu beschleunigen? Oder dass nur die neu dazugekommenen Excel-Dateien hinzugefügt werden zur Mastertabelle?
Viele Grüße
Lukas Stallkamp
Hallo Lukas,
die Abfragezeit hängt natürlich tatsächlich sehr von den Datenmengen ab. Eine Möglichkeit, nur neu dazugekommene Dateien zu behandeln, ist grundsätzlich denkbar. Dazu musst da aber mehrere getrennte Abfragen einrichten: Eine für die Mastertabelle und eine für die neu hinzugekommenen Datensätze. Und diese werden dann an die Mastertabelle über eine Anfügeabfrage angehängt.
Um nur neu hinzugekommene Dateien zu berücksichtigen, kannst du in der Importabfrage einen Datumsfilter auf das Erstellungsdatum legen („Date created“). Da musst du einfach ein wenig mit den Filtervarianten experimentieren.
Schöne Grüße,
Martin
Hallo Martin,
ich habe gerade eine schöne Aufgaben mehrere CSV-Dateien (100+) mit Messwerten einzulesen. Die Dateien sind alle gleich aufgebaut; Zeitstempel, Messwert, Messstellen ID. Das geht zwar mit Power Query fast so wie ich es brauche aber leider sind es mehr als 1.048.576 Zeil in Summe.
Ich müsste Power Query dazu bringen, dass die Daten nicht untereinander kopiert werden (anfügen) sondern nebeneinander. Also so wie das bei der Funktion Zusammenführen passiert. Die Zeitstempel sind alle gleich bzw. lassen sich so anpassen, dass sie in allen Datei gleich sind.
Meine Idee das Problem zu lösen, wäre jetzt jede Datei einzeln einzulesen als Abfrage und dann mit Abfragen Zusammenführen alle nebeneinander zu bekommen. Ist bei der Anzahl an Dateien nur sehr aufwendig…
Hast du eine Idee wie man das smarter Lösen kann?
Schöne Grüße
Fritz
PS: Sehr schöner Blog 🙂
Hallo Fritz,
danke für das nette Feedback 🙂
Was den Massenimport angeht: Die Dateien alle sozusagen nebeneinander zusammenzuführen, dürfte schwierig werden. Dafür kann ich keine Lösung bieten. Aber das ist meiner Ansicht nach auch gar nicht unbedingt notwendig (wer will sich schon so viele Daten im Detail anschauen?).
Mit Power Query könntest du alle Dateien untereinander einlesen und dann nur eine Verbindung erstellen und aus dieser Verbindung beispielsweise eine Pivot-Tabelle generieren. Die eigentlichen Daten werden dann alle im Hintergrund gehalten und sind in der Excelmappe nicht sichtbar. Du musst im Power Query-Editor nur sagen „Schließen und laden in…“ und dann eben „PivotTable-Bericht“ auswählen.
Wäre das eine Lösung?
Schöne Grüße,
Martin
Hallo Martin,
danke für deinen Tipp. Habe es jetzt fast so gemacht wie du es gesagt hast. Habe vorher nur noch die Daten in PowerQuery aufbereitet (Tageswerte aus kumulierten Werten berechnen, etc.) und habe die Daten nicht wie im Text beschreiben kombiniert sonder über die Anleitung zum Masseneinlesen von Exceldatei kombiniert. Dadurch habe ich die Information behalten aus welcher Datei die Werte stammen und genau diese Info brauche ich für das weitere Auswerten mit Pivot.
Schöne Grüße
Fritz
Hallo.
Sehr schöner Blog.
Ich habe mal eine speziellere Frage.
Ich habe einen Ordner mit txt Dateien (300+). Aus diesen möchte ich nun 3 spezielle Zeilen (immer gleich, durch leerzeichen getrennt) in Excel importieren.
Ist es möglich Excel genaue Vorgeben der Zeilen zu machen und er übernimmt diese dann für alle restlichen?
Wenn ja wie genau?
Danke schon mal.
Gruß
Hallo Peter,
wenn die Dateien alle identisch aufgebaut sind, das heißt, wenn die 3 besagten Zeilen in allen Dateien immer an der gleichen Stelle liegen, dann sollte das schon möglich sein. Man kann z.B. über die Transformation „Zeilen entfernen“ angeben, dass man die ersten X (1, 3, 10…) Zeilen löschen möchte. Oder diese besagten Zeilen haben ein spezielle Attribut, nach dem man dann filtern kann. Dann spielt die Position keine Rolle.
Es sind also unterschiedliche Ansätze denkbar, je nach konkreter Datenlage.
Schöne Grüße,
Martin
Hallo Martin,
weißt du, ob bzw. wie es möglich ist, bei allen zu importierenden (gleich aufgebauten) Exceldateien immer jeweils die ersten 28 Zeilen zu löschen? Wenn ich über den Befehl „Erste Zeilen entfernen“ gehe, führt er das leider nur für die erste Datei aus.
Vielen lieben Dank für deine Unterstützung!
Liebe Grüße, Julia
Hallo Julia,
ja, das lässt sich ohne Problem einrichten. Du musst die Transformation im Power Query-Editor nur schon in der Abfrage für die Beispieldatei vornehmen, und nicht erst in der finalen Abfrage. Im Screenshot zu meinem Artikel oben wäre das die Abfrage „Beispieldatei aus Import-1 transformieren“. Denn diese Beispielabfrage wird dann auf alle einzelnen Dateien angewendet.
Schöne Grüße,
Martin
Hallo,
ich nutze Power Query schon eine ganze Weile und bin begeistert.
Allerdings verzweifele ich an der Bearbeitungsdauer bei manchen Dateien. Ich habe z.B. in csv (40 MB mit 400.000 Zeilen) importiert und 10-15 Transformationen definiert. Das Laden und Aktualisieren dauert da schon mal locker ein paar Minuten. Auch wenn ich die Abfrage bearbeiten will, dauert das Anzeigen der Vorschau bei jedem Schritt ewig.
Dabei macht es keinen Unterschied, ob die Datei lokal auf dem Rechner oder online in einem Sharepoint Ordner liegt.
Ist das normal oder hast du dazu zufällig einen Tipp? Stoßt Excel mit Power Query da schon an die Grenzen?
VG Stephan
Hallo Stephan,
pauschal lässt sich das nur schwer beantworten, grundsätzlich ist es aber schon so, dass sehr große Datenmengen länger dauern. Wobei der reine Import von 400.000 Zeilen kein großes Problem sein sollte. Es hängt natürlich auch von den angewendeten Transformationen ab. Vielleicht gibt es ja da noch Optimierungspotenzial.
Schöne Grüße,
Martin
Hallo Martin,
ja ich vermute es liegt wirklich an den angewendeten Transformationen. Hier scheint nach ein paar Tests v.a. das Pivotisieren als Transformation die Ursache der Verlangsamung zu sein. Lösche ich die Pivot Schritte raus, gehts wieder deutlich schneller.
Gruß, Stephan
Hallo Martin,
vielen Dank für die tolle Beschreibung.
Ist es möglich nachträglich die Beispieldatei zu ändern? Also nicht die „erste Datei“ sondern eine andere mit speziellen Namen?
Vielen Dank und viele Grüße
Susanne
Hallo Susanne,
ja, das geht. In der Abfrage „Beispieldatei“ kannst du im Schritt „Navigation“ entweder die laufende Dateinummer oben in der Bearbeitungszeile ändern. Also zum Beispiel
von
= Quelle{0}[Content]
in
= Quelle{2}[Content]
Dann wird die dritte Datei in der Liste verwendet. Es wird nach der Änderung dann nur ein zusätzlicher Schritt „Importierte Excel-Datei“ erzeugt, den du löschen musst.
Und wenn es wirklich ein fixer Dateiname sein soll, dann änderst du den Schritt „Navigation“ so ab:
= Quelle{[#“Folder Path“=“C:\Temp\Import-1\“,Name=“Mär.xlsx“]}[Content]
Natürlich auf deine Verhältnisse angepasst. Und auch hier wieder den zusätzlichen Schritt „Importierte Excel-Datei“ löschen.
Schöne Grüße,
Martin
Hallo, ich möchte mich mal bedanken, für Deine tollen Erklärungen sowie Videos.
Ein kurze Frage: In meiner Excel-Version werden die Tabellennamen der ins Model eingelesenen Dokumente bei der Ausgabe ins Excelsheet als neuer Tabellennamen dort auch übernommen. Das ist auf einem anderen Rechner mit einer anderen 365iger Version leider nicht so. Kann man das in den Optionen einstellen?
Viele Grüße, Peter
Hallo Peter,
wir sprechen hier vom Namen der formatierten Tabelle, richtig? Und nicht vom Namen des Tabellenblatts. Ich kenne es auch nur so, dass der Name der Abfrage gleichzeitig auch als Name der formatierten Tabelle übernommen wird. Und mit ist auch keine Einstellung bekannt, mit der man das ändern könnte.
Schöne Grüße,
Martin
Hallo Martin,
ich möchte mit deiner Methode gerne mehrere absolut identisch aufgebaute Excel-Dateien zusammenführen, bekomme dabei aber jedes Mal eine Fehlermeldung angezeigt.
Kann es sein, dass der Tabellenblattname in den zu importierenden Dateien immer gleich lauten muss – das ist bei mir nämlich nicht der Fall?
Kann man dieses Problem irgendwie umgehen?
Grüße
Thomas
Hallo Thomas,
ja, die unterschiedlichen Blattnamen dürften mit großer Wahrscheinlichkeit die Ursache sein. Beim Massenimport wird ja eine Beispieldatei als Referenz genommen und damit auch der dort verwendete Blattname.
Man kann das umgehen, wenn man mit formatierten Tabellen arbeitet. Die können dann auch auf unterschiedlich benannten Arbeitsblättern liegen. Lediglich die formatierten Tabellen müssen den gleichen Namen haben.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine Seite mit sehr vielen nützlichen Tipps. Leider habe ich bisher die Lösung auf mein Problem noch nicht gefunden und ich hoffe du kannst mir weiterhelfen.
Ich habe eine große Anzahl von csv Dateien mit je 2 Spalten. Die erste Spalte ist immer gleich nur die 2. enthält jeweils neue Daten. Ich würde nun gerne alle csv Dateien so importieren, dass die erste Spalte einmal am Anfang erscheint und danach immer die zweite Spalte aus allen anderen Dateien in den nachfolgenden Spalten der Zieldatei erscheinen. Leider bekomme ich es nur hin, dass die Datensätze untereinander geschrieben werden…ich jedoch benötige sie „nebeneinander“.
Gibt es hierfür eine Möglichkeit?
Danke und Gruß
Daniel
Hallo Daniel,
wenn du die Daten in Power Query sozusagen untereinander importiert hast, gibt es ja eine Spalte Source.Name, die den Namen der jeweiligen importierten Datei enthält. Du kannst dann diese Spalte markieren und über das Menü „Transformieren | Spalte pivotieren“ die Tabelle umwandeln. Als Wertespalte wählst du dann die Spalte aus, die die eigentlichen Werte enthält. Das sollte funktionieren.
Schöne Grüße,
Martin
Hallo Martin,
super, vielen Dank für deinen Hinweis. So funktioniert es.
Gruß
Daniel
Dann lass es uns unten in den Kommentaren wissen………
Hallo Martin
Aus einem Ordner Jahresrechnung/Jan-Dez,
(jan_21.xlsx/feb_21.xlsx/maerz_21.xlsx, etc.)
habe ich bei jedem Monat/Tabelle, den Saldo ausgewiesen. „=summe(L2:L212)“.
In Spalte/Zeile K212, jeweils einen Feldbezeichner bsp. „ER_jan_21“ vorangestellt.
Nun möchte ich mit PQ, von jedem Monat nur K212 und L212 in eine neue Tabelle/neuerOrdner/ER21, bringen/filtern.
Hast Du hiefür einen Ansatz („individueller datenfeld/filter“ aus gleichen Datenstrukturen/*.xlsx)
Liebe Grüsse aus Passau/BayWa
Karl_Heg
Hallo Karl,
das hört sich für mich einfach nach einem Filter an. Wenn die besagte Zeile mit dem Feldbezeichner einen Inhalt wie „ER_jan_21“ hat, dann könntest du diese Spalte mit einem Textfilter filtern: Text beginnt mit „ER_“
Schöne Grüße,
Martin
Hallo Martin,
in meinem Fall (Kostenstellenreports) ändern sich Kostenstellennummern regelmäßig. Mal fliegen Kostenstellen raus (das erzeugt dann in der Ordnerabfrage die Fehlermeldung: „Fehler in der Abfrage „Datei transfomieren“. Expression Error: Die Spalte „4711“ der Tabelle wurde nicht gefunden“), mal kommen neue Kostenstellen dazu, die aber nicht eingelesen werden, weil sie in der Beispieldatei nicht vorkommen.
Hast Du vielleicht einen Lösungsansatz?
Vielen Dank.
Martin
Hallo Martin,
das ist nicht ganz trivial und es gibt nicht die eine perfekte Lösung. Es kommt darauf an, an welcher Stelle in der Abfrage genau der Fehler auftritt. Oftmals ist das der Schritt, wo das Datenformat festgelegt wird und das zu einem Fehler führt, weil die Spalte nicht mehr vorhanden ist. Wenn man hoch dynamische Tabellenstrukturen hat (Spalten kommen dazu, andere fliegen raus), wird es manchmal schwierig. Eventuell hilft es, die Spaltentypen nur für die immer vorhandenen Spalten festzulegen. Oder man führt die Typenkonvertierung erst ganz am Ende durch, wenn beispielsweise schon eine Entpivotierung stattgefunden hat.
Aber das hängt natürlich von der konkreten Situation ab, einen pauschalen Tipp kann ich dir aus der Ferne leider nicht geben.
Schöne Grüße,
Martin
Hallo Martin,
ich danke dir für die schnelle Antwort. Das mit der Typenkonvertierung ist ein guter Tipp. Wenn der Spaltenname irgendwo im Code fest abgefragt wird, hat man ja schon verloren, wenn nicht sichergestellt ist, dass dieser Spaltenname in allen Dateien des Ordners vorhanden ist. Ich bin gespannt, ob deine Lösungsansatz funktioniert. Ich melde mich wieder bei dir, wenn ich es ausprobiert habe.
Bis dahin.
Viele Grüße
Maritn
Hallo Martin (ich nehme an, das „Du“ ist ok 🙂 )
Ich habe das Problem, mit MS-Query aus einer Oracle Tabelle mehr als 256 Spalten in Excel einlesen zu müssen. Oracle kann das, Excel, bzw. Query moppert. Sinngemäß: Mehr als 256 Felder sind nicht drin. Schade. Obwohl eine Excel-Tabelle selbst ja wesentlich größer sein darf.
Hast Du eine Idee oder mache ich was falsch?
Vielen Dank schomal,
Tom
Hallo Tom,
klar ist das „Du“ ok 🙂
Es ist gut möglich, dass es in MS-Query einfach diese Beschränkung gibt, genau weis ich das leider nicht.
Mein Tipp daher: Nutze stattdessen Power Query, dort existiert diese Beschränkung nicht. Es sei denn, man importiert aus *.xls oder *.xlsb-Dateien, dann gelten auch hier 255 Spalten als Limit (siehe auch Power Query Spezifikationen und Grenzwerte in Excel
Schöne Grüße,
Martin