Power Query ist eines der genialsten Werkzeuge überhaupt, die in Excel zur Verfügung stehen. Für das Importieren, Aufbereiten und Bereinigen von Daten gibt es nichts besseres! Allerdings kann man auf Probleme stoßen, wenn man die über Power Query in ein Tabellenblatt geladenen Daten um manuell einzugebende Informationen ergänzen möchte.
So ist es zwar grundsätzlich möglich, der Tabelle einfach eine weitere Spalte hinzuzufügen, um dort beispielsweise Bemerkungen einzutippen. Beim nächsten Aktualisieren der Abfrage kann es aber ein böses Erwachen geben, wenn diese Bemerkungen dann vielleicht an der falschen Stelle stehen!
Warum und unter welchen Umständen das Problem auftreten und wie man es lösen kann, zeige ich in diesem Beitrag.
Und so geht’s:
Was war zuerst: Henne oder Ei?
Die ideale Lösung für das eingangs beschriebene Szenario wäre natürlich, dass man die Bemerkungen direkt in der Quelldatei ergänzt, und nicht erst in der importierten Tabelle.
Im echten Leben ist das jedoch nicht immer möglich oder praktikabel. Die Quelldatei wird vielleicht immer wieder ersetzt, weil beispielsweise neue Datensätze dazukommen. Das heißt, sie enthält immer alle bisherigen Datensätze – die wir aber in Excel schon um Bemerkungen ergänzt haben – und darüber hinaus auch noch neue.
Und dadurch kommt es beim Aktualisieren der Abfrage in Excel zu einem typischen Henne-Ei-Problem:
Die bereits importierten und manuell ergänzten Daten müssten mit dem neuen Import zusammengeführt werden. Oder andersherum, die neu zu importierenden Daten müssten zuerst die schon vorhandenen Daten auslesen und dann wieder in die Tabelle geladen werden…
Die Lage ist verzwickt und wir drehen uns im Kreis!
Dann wäre vielleicht mein Einsteigerkurs etwas für dich. Alle Details und Termine findest du auf dieser Seite:
Daten importieren und aufbereiten mit Power Query
Ein kleines Praxis-Beispiel
Ich habe mir dazu folgendes Szenario überlegt. Der Vertrieb bekommt von der Buchhaltung regelmäßig eine Liste mit überfälligen Rechnungen, um diese bei seinen Kunden nachzutelefonieren. Diese Liste soll dazu in Excel importiert und dort zu jeder Rechnung ein Kommentar zum aktuellen Stand eingegeben werden. Und natürlich gibt es nächste Woche eine neue Liste mit allen überfälligen Rechnungen. Die bereits hinterlegten Kommentare sollen beim neuen Import natürlich nicht verlorengehen.
So sieht diese kleine OP-Liste aus, die wöchentlich von der Buchhaltung bereitgestellt wird:
Meine kleine Offene-Posten-Liste
Der in Excel versierte Vertriebskollege importierte die Liste mit Power Query in eine separate Arbeitsmappe. Da er sich um die größeren Brocken zuerst kümmern möchte, sortiert er die Tabelle in Excel absteigend nach dem Rechnungsbetrag und ergänzt dann in einer neu hinzugefügten Spalte ihre Kommentare:
Importierte Tabelle wird um manuelle Kommentare ergänzt
Allerdings gibt es bald eine böse Überraschung:
Wenn die OP-Liste nämlich beim nächsten Mal neu eingelesen wird (Menü „Daten | Alle aktualisieren“), passen die Kommentare nicht mehr zu den Rechnungen!
Nach dem Aktualisieren stimmt die Zuordnung nicht mehr
Der Grund dafür ist, dass die Kommentare nicht logisch mit den Rechnungsnummern verknüpft sind. Da manuell hinzugefügte Spalte wird im Grunde so behandelt, als wäre sie eine separate Spalte rechts neben der Tabelle, die keinen direkten Bezug zur OP-Liste hat.
Wie lässt sich dieses Problem beheben?
Die Lösung: Selbst-referenzierende Abfragen
Kurz gesagt muss dazu die zu importierende Offene-Posten-Liste mit der bereits importierten und bearbeiteten Tabelle in Power Query zusammengeführt und erst dann wieder in das Tabellenblatt ausgespielt werden.
Beginnen wir also nochmal von vorne.
Über das Menü „Daten | Daten abrufen | Aus Datei | Aus Excel-Arbeitsmappe“ wird die OP-Liste ausgewählt und importiert:
OP-Liste mit Power Query importieren
Die importierte Liste…
Der Einfachheit halber gehe ich in diesem Beispiel davon aus, dass keine weiteren Bereinigungsmaßnahmen notwendig sind in die Daten direkt und ohne Umwege in ein Arbeitsblatt geladen werden können.
Im nächsten Schritt füge ich in der geladenen Tabelle manuell eine Kommentarspalte hinzu. Weil ich mich zuerst auf die größeren Beträge stürzen möchte, sortiere ich die Tabelle absteigend nach Betrag und ergänze dann meine Kommentare. Außerdem benenne ich die Abfrage gleich noch um in „OP-Liste“:
… wird sortiert und um eine Kommentarspalte erweitert
Hinweis:
Die Sortierung ist natürlich nicht zwingend notwendig. Aber damit kann man später gleich erkennen, ob die Kommentare noch richtig zugeordnet sind.
Diese Tabelle wird erneut über „Daten | Aus Tabelle/Bereich“ in Power Query eingelesen. Zur besseren Übersicht benenne ich sie im Power Query-Editor auch gleich noch um in „OP-Liste-Kommentare“:
Importierte Tabelle wird erneut nach Power Query geladen
Und jetzt kommt der große Trick!
Ich wechsle in die erste Abfrage „OP-Liste“ und führe diese mit der zweiten Abfrage „OP-Liste-Kommentare“ zusammen. Das gemeinsame Schlüsselfeld ist dabei die Rechnungsnummer:
Zusammenführen der ersten mit der zweiten Abfrage
Die zusammengeführte OP-Liste
… und blende mir dann nur die „Kommentare“-Spalte ein:
Nur die Kommentar-Spalte auswählen
OP-Liste mit Kommentaren
Das war’s auch schon. Da die zweite Abfrage „OP-Liste-Kommentare“ nicht ins Arbeitsblatt geladen werden soll, verlasse ich den Power Query-Editor über „Start | Schließen & Laden | Schließen & Laden in…“ und wähle im nächsten Fenster die Option „Nur Verbindung erstellen“:
Schließen & laden in…
Nur eine Verbindung erstellen
Meine Tabelle hat jetzt allerdings 2 Kommentar-Spalten. Die rechte davon („Kommentare2“) ist diejenige, die ich ursprünglich manuell ergänzt hatte. Man sieht auch schon, dass hier die Reihenfolge nicht mehr passt. Diese Spalte wird nicht mehr benötigt und kann daher einfach gelöscht werden.
Spalte „Kommentare“ kann gelöscht werden
Nun kommt die Probe auf’s Exempel. Ich ergänze die ursprüngliche OP-Liste.xlsx um ein paar weitere Rechnungen:
OP-Liste wird um weitere Rechnungen ergänzt
Anschließend wird meine importierte Tabelle per Rechtsklick aktualisiert. Und wie durch ein Wunder funktioniert alles wie gewünscht. Die neuen Datensätze werden in die bisherige Tabelle einsortiert, die Kommentare befinden sich weiterhin an der richtigen Stelle:
Der Import nach dem erneuten Aktualisieren
Fazit
Auch wenn man sich am Anfang beim Nachdenken über das Problem vielleicht ein wenig im Kreis gedreht hat, stellt sich die Lösung letztlich als recht einfach dar.
Die zwingende Voraussetzung für eine erfolgreiche Umsetzung dieser Technik ist jedoch, dass jeder Datensatz ein eindeutiges Merkmal hat. In unserem Fall war das die eindeutige Rechnungsnummer. Ohne dieses eindeutige Kriterium wäre es nicht möglich, die Kommentare zuverlässig den einzelnen Datensätzen zuzuordnen.
Für einen solchen Fall könnte man eventuell versuchen, eine Hilfsspalte einzurichten, in der alle anderen Spalten verkettet werden. Auf diese Weise könnte man eventuell ein eindeutiges Kriterium erhalten. Aber das ist sehr mit Vorsicht zu genießen und funktioniert sicherlich nicht in jedem Anwendungsfall.
Was hältst du von dieser Lösung? Lass es uns 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.
Lieber Tabellenexperte,
das ist genial. Eine solche Möglichkeit habe ich schon länger gesucht.
Ist ja quasi eine Art Zirkelbezug :-), aber einer, der funktioniert.
Vielen Dank
Hallo Maic,
ja, das Bild mit dem funktionierenden Zirkelbezug trifft es ganz gut 🙂
Schöne Grüße,
Martin
Hallo Martin,
habe ich tatsächlich auch schon so gebaut. Aber wie immer richtig gut erklärt, so dass ich dies auch den nicht so affinen Kollegen 1:1 weiterleiten kann 😉
Warum willst du eine Hilfsspalte bauen, in der du mehrere Kriterien verkettest? Das Mapping in PowerQuery kann ich ja auch über mehrere Spalten machen. Den Schritt mit der Hilfsspalte, die ich danach eh wieder lösche, kann ich mir meiner Meinung nach somit sparen 😉
Hallo Matthias,
du hast natürlich vollkommen recht! Anstelle einer Hilfsspalte in der Tabelle lässt sich das natürlich auch in Power Query ganz elegant lösen.
Schöne Grüße,
Martin