Massen-Import mit PowerQuery 56

Artikelbild-198
Die Inhalte vieler Dateien mit wenigen Klicks in eine einzige Tabelle importieren.
 

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

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

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

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

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

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

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

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

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

Abfrage bearbeiten

Dort kann man über das Dropdown-Feld der Spalte „Datum“ die Liste aufsteigend sortieren lassen:

Sortieren der Datumsspalte

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

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.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Daniel Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

56 Gedanken zu “Massen-Import mit PowerQuery

  • Avatar-Foto
    Tim

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Stephan

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Stephan

        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

        • Avatar-Foto
          Martin Weiß

          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

          • Avatar-Foto
            Stephan

            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

          • Avatar-Foto
            Martin Weiß

            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

  • Avatar-Foto
    Richard

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Richard

        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

        • Avatar-Foto
          Martin Weiß

          Hallo Richard,

          genau so soll es sein: Tipps aufgreifen, damit herumexperimentieren und tolle Lösungen finden!

          Schöne Grüße,
          Martin

          • Avatar-Foto
            Tino Rompel

            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

          • Avatar-Foto
            Martin Weiß

            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

  • Avatar-Foto
    Kathrin

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Tim2

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Tim2

        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

        • Avatar-Foto
          Andrea

          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

  • Avatar-Foto
    Thomas Thiede

    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

    • Avatar-Foto
      Martin Weiß

      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

        • Avatar-Foto
          Martin Weiß

          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

        • Avatar-Foto
          Simone

          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!

  • Avatar-Foto
    Mario Eisenmajer

    Hallo,

    wie kann ich bei dieser Funktion die Überschriften der Tabellen definieren?
    Meine Exeldatei ist so komplex, dass Query sie nicht erkennt.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Haastert

    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 ?

    • Avatar-Foto
      Martin Weiß

      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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Fritz

    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 🙂

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Fritz

        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

  • Avatar-Foto
    Peter Kühlein

    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ß

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Julia Mittermüller

        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

        • Avatar-Foto
          Martin Weiß

          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

  • Avatar-Foto
    Stephan

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Stephan

        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

  • Avatar-Foto
    Susanne

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Specht

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Thomas Föller

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Daniel

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Hegetschweiler Karl

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Martin Hölscher

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Martin Hölscher

        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