Xtract: Mit Power Query verschiedene Listen auf Unterschiede vergleichen (Produkt fehlt, überschüssig, Änderung). Dabei kommen unterschiedliche Join-Art Bedingungen zur Anwendung.
Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
Hinweis: Die prinzipiell gleiche Aufgabenstellung, dort allerdings auf der Basis von *.csv-Dateien wurde hier im Forum diskutiert. In großen Teilen ist der Inhalt identisch, manches ist hier (in diesem Beitrag) vielleicht detaillierter dargestellt, damit auch Einsteiger in Sachen Power Query (die aber solide Erfahrung in Excel haben) besser zum Ziel kommen.
Basierend auf einer in dieser Form immer wieder gestellten Frage in Excel-Foren habe ich einige Test-Datensätze generiert. Es sind in diesem Fall 2 *.xlsx-Files. Die Quelle(n) für die Daten: Ein internes und ein externes Lager, welche täglich ihren Bestand in die Zentrale melden. Auf der Basis dieser beiden Files sollen nun durch Excel, genauer gesagt durch Power Query 3 Ergebnisse berechnet werden:
- Fehlende Datensätze (Produkte)
- Überzählige Datensätze (fehlen in der 2. Datei)
- Datensätze mit Änderungen innerhalb der Daten
Folgende beiden Dateien liegen nach dem entpacken als Excel-File vor: Lager HH.xlsx und Lager RZ.xlsx. Ich habe die beiden Files im Verzeichnis C:\Data\ abgelegt; dieses Wissen ist für sie wichtig, wenn sie zum Vergleich meine fertige Auswertung laden. In dieser (zip-gepackten) Datei finden Sie die beiden Quelldateien als auch meine Muster-Lösung. Ach ja, Lager HH ist das Haupt-Lager, von wo aus fehlende Produkte zum Lager RZ transferiert werden.
Fehlende Datensätze (Produkte)
Beginnen Sie damit, nacheinander jede der beiden Excel-Files in eine leere Excel-Mappe zu importieren (Vorgehensweise ab Excel 2016⁄365): Daten | Neue Abfrage | Aus Datei | Aus Arbeitsmappe. Im File-Dialog wechseln Sie zum Verzeichnis, wo die xlsx-Dateien abgelegt sind; hier ist es ‑wie bereits erwähnt- C:\Data. Nach einem Klick auf Lager HH.xlsx öffnet sich der Navigator-Dialog erst einmal ziemlich leer. Markieren Sie das einzige existierende Tabellenblatt (Bestand HH) im linken Fenster-Bereich, dann zeigt sich rechts eine Vorschau der Daten. So können Sie bei mehreren Arbeitsblättern (oder Tabellen) die Auswahl präzisieren bzw. überprüfen, ob es die richtigen Daten sind:
Vorschau der Daten für den Import
Bei der Gelegenheit ein Hinweis: Die meisten Menü-Schritte und Screenshots (Bildschirmfotos) sind aus der bzw. passen zur 2019er Version des Excel. Bei markanten Unterschieden sind auch die Abläufe bzw. Screenshots der 2016er Version mit aufgezeigt.
Erweitern Sie die Schaltfläche Laden ▼ und wählen Sie dort den Punkt Laden in… :
Klicken Sie nicht auf die Schaltfläche sondern erweitern Sie diese und wählen Laden in…
Und im folgenden Dialog markieren Sie Nur Verbindung erstellen:
Nur eine Verbindung erstellen (Version 2019)
Etwas andere Darstellung in Excel 2016 für das Laden in…
Dadurch erstellen Sie jeweils eine Abfrage, ohne dass diese nach einem Schließen & laden in eine Tabelle geschrieben wird. Dass sich der Power Query-Editor direkt danach schließt, ist der normale Ablauf. Gehen Sie bei der zu vergleichenden Datei (Lager RZ) den gleichen Weg. Im rechten Seitenfenster (Arbeitsmappenabfragen) sind anschließend beide Abfragen mit dem Vermerk Nur Verbindung aufgeführt.
Öffnen Sie nun die Abfrage Bestand HH beispielsweise durch einen Doppelklick auf den Eintrag im rechten Seitenfenster. Wählen Sie im Menü Start die Schaltfläche Kombinieren und erweitern Sie den Menüpunkt Abfragen zusammenführen ▼ durch einen Klick auf das Dreieck. Wählen Sie hier Abfragen als neue Abfrage zusammenführen. Unterhalb der Auflistung mit den ersten Daten aus Bestand HH Klicken Sie in das leere Textfeld/Kombinationsfeld und wählen dort die Abfrage Bestand RZ. Markieren Sie nun jeweils ein beliebiges Feld in der Spalte ProduktNr.; ganz unten im Fenster wird Ihnen angezeigt, dass die Auswahl mit 1.246 der ersten 1.267 Zeilen übereinstimmt:
Die ersten Schritte beim Zusammenführen der Daten
In der ersten Fragestellung geht es ja darum, welche Datensätze zwar in der Abfrage Bestand RZ vorhanden sind, jedoch in der Abfrage Bestand HH fehlen. Also was kann eventuell vom Außenlager in das Zentrallager überführt werden? Dazu erweitern Sie das Kombinationsfeld Join-Art und wählen Rechter Anti-Join (Zeilen nur in zweiter). Nach einem Klick auf die Schaltfläche OK ist die Abfrage auf eine Zeile geschrumpft und scheinbar stehen dort keine der gewünschten Daten drin:
Vielleicht etwas irritierend, dennoch korrekt diese Darstellung
Der Eindruck täuscht. Erweitern Sie das Feld Bestand RZ 
durch einen Klick auf den Doppelpfeil in der Überschrift und Entfernen Sie im Dialog nur das Häkchen im alleruntersten Punkt Ursprünglichen Spaltennamen als Präfix verwenden. Schließen Sie nun das Fenster mit OK. Idealerweise löschen Sie nun die ersten 5 Spalten (sie enthalten ja sowieso nur den Wert null) und entfernen in den verbliebenen Überschriften jeweils die letzten beiden Zeichen (.1), damit immer nur die jeweils „reine” Überschrift ProduktNr. .. Wert stehen bleibt. Normalerweise werden Sie nun der Deutlichkeit wegen den Namen der Abfrage von Merge1 auf beispielsweise Fehlend in HH ändern. Hinweis: Änderungen bei Überschriften und Namen lassen sich gut erledigen, wenn Sie einen Doppelklick darauf durchführen oder nach dem Markieren F2 drücken. Und natürlich bietet sich stets ein Rechtsklick an … 😉
Für die Abfrage mit der zweiten Fragestellung gehen Sie prinzipiell genauso vor. Öffnen Sie wiederum die Abfrage Bestand HH, was hier übrigens ganz leicht durch einen Klick auf den entsprechenden Eintrag im linken Seitenfenster geht. Wiederum Abfragen als neue Abfrage zusammenführen und beide Spalten der Produkt-Nr. - Column markieren. Da die Fragestellung dieses Mal jene Datensätze erfassen soll, die in der 2. (unteren) Abfrage fehlen, wählen Sie bei Join-Art den vorletzten Punkt Linker Anti-Join (Zeilen nur in erster) das Ergebnis wird sich so darstellen:
Nicht nur die Zahl der Datensätze ist erheblich größer, auch das Handling ist anders
Die gewünschten Daten stehen ja bereits in den ersten 4 Spalten, darum können Sie die die letzte Spalte Bestand RZ sofort löschen. Geben Sie nun diese Abfrage noch einen „sprechenden” Namen, beispielsweise Überzählig in HH.
Datensätze mit Änderungen innerhalb der Daten
Auch wenn diese letzte Fragestellung etwas mehr Aufwand bedeutet wird es sie nicht wundern, dass die ersten Schritte mit dem Kombinieren der beiden Abfragen identisch sind. Als Join-Art wählen Sie hier den Inner Join (nur übereinstimmende Zeilen). Erweitern Sie die 6. Spalte durch einen Klick auf den Doppelpfeil und sie brauchen nur darauf zu achten, dass das Häkchen beim Kontrollkästchen Ursprünglichen Spaltennamen… nicht gesetzt ist. Die Abfrage besteht jetzt aus 10 Spalten, wobei die Überschriften der 2. Hälfte jeweils den Zusatz .1 tragen. Sie können schon an dieser Stelle den Namen der Abfrage ändern, beispielsweise auf Unterschiede.
Wählen Sie nun den Menüpunkt Spalte hinzufügen und dort im Menüband Benutzerdefinierte Spalte. Sie können den neuen Spaltennamen so belassen oder wenn Ihnen das lieber ist beispielsweise identisch eingeben. In das Feld mit der Spaltenformel geben Sie nach dem vorgegebenen = diese Funktion (gerne auch in einem Rutsch ohne Zeilenumbruch) ein:
= [#"ProduktNr."]=[#"ProduktNr..1"]
and [Produkt]=[Produkt.1]
and [Anzahl]=[Anzahl.1]
and [Preis]=[Preis.1]
Die jeweiligen Spaltennamen in den eckigen Klammern übernehmen Sie vorzugsweise durch einen Doppelklick auf den Namen der Spalte im rechten Rahmen, wodurch die eckigen Klammern und bei Bedarf auch die vorangestellte Raute # automatisch mit eingefügt werden. Das Ergebnis ist eine neue Spalte, wo entweder der Wert TRUE oder FALSE steht.
… Oder stehen sollte. Fällt Ihnen etwas auf? Gleich in der 1. Zeile hat sich in der Spalte Produkt. 1 ein Fehler (Error) eingeschlichen. Und wenn Sie weiter nach rechts scrollen werden Sie erkennen,dass dieser Fehlerwert auch in der Spalte identisch steht. na gut, sie kennen gewiss für die Vorgehensweise. Na gut, sie kennen gewiss schon die Vorgehensweise. Ein Klick in die Zelle mit dem Fehlerwert und ihnen wird eine mehr oder weniger aussagekräftige Fehlermeldung angezeigt: DataFormat.Error: Ungültiger Zellenwert ‘#REF!’. Nun ja, das ist die englischsprachige Meldung #REF! welche im deutschen Excel mit #Bezug! ausgedrückt wird. Und da diese Meldung in der Spalte auftaucht, welche aus der Abfrage Bestand RZ kommt, wird dort auch der Fehler liegen.
Sie ahnen es gewiss, um die Abfrage wird als Nur Verbindung gespeichert. Dazu Datei | Schließen & laden in… und die Einstellungen entsprechend ihres Wunsches vornehmen. Im rechten Seitenfenster erkennen sie nun auch beim untersten Eintrag Merge1 das Warnsymbol links des Namens. Der Fehler sollte in jedem Fall beseitigt werden, schließlich stehen in der Zeile ja sinnvolle Daten. Öffnen Sie die Datei Lager RZ.xlsx und suchen Sie die Zeile, wo in der Spalte ProdukNr. die ID 7 steht. Und um Ihnen eine lange Suchen zu ersparen, es ist die Zeile 953. 😎 Sie werden schnell erkennen,
Sie werden sich zwar denken können, dass die fehlende Produktnummer die 537 ist. Aber sicher ist das nicht. Darum sollten Sie auch Lager HH.xlsx öffnen und nachsehen, welches Produkt zur Produktnummer 7 gehört. OK, jetzt haben Sie auch die Bestätigung, dass die Vermutung stimmt. Korrigieren Sie den Inhalt der Zelle entsprechend und schließen Sie beide Lager-Files, wobei das Speichern nach der Änderung wichtig ist! Wenn Sie nun in der aktuellen Arbeits-Datei mit den PQ-Abfragen die Query Merge1 öffnen werden Sie erkennen, dass sich trotz des Behebens des Fehlers immer noch der falsche Wert angezeigt wird. Löschen Sie erst einmal im rechten Seitenfenster die Zeile unter Hinzugefügte benutzerdefinierte… und es tut sich leider immer noch nichts; was aber Besserung bringt: Start | Vorschau aktualisieren. 🙂 Last but not least benennen Sie die Query Merge1 in beispielsweise Unterschiede um.
Filtern Sie nun in dies Spalte so, dass der Wert FALSE erhalten bleibt und es werden jene Zeilen übrig bleiben, wo mindestens ein Wert in den kommunizierenden Spalten unterschiedlich ist. Sie können sich nun entscheiden, ob Sie alle 8 Spalten zu Vergleichszwecken bestehen lassen wollen oder ob sie sich auf die ersten 4 Spalten für die Darstellung in der endgültigen Abfrage beschränken. Entfernen Sie also nun nur die letzte Spalte (identisch) oder auch die 4 Spalten davor, falls Sie nur die Daten der des Lagers HH darstellen wollen.
Auch wenn Sie jetzt auf Schließen & laden Klicken, wird die Abfrage als (nur) Verbindung gespeichert, denn dieser Status ist in Power Query ja fixiert. Alle Abfragen liegen jetzt als Nur Verbindung vor. Da es sinnvoll erscheint, die 3 Vergleichs-Abfragen in je einem Arbeitsblatt darzustellen, wandeln Sie diese Query ist wie bereits oben beschrieben so um, dass sie in je ein Tabellenblatt gespeichert werden. Schlussendlich werden sie die Arbeitsblätter noch sinnvoll umbenennen.
Hinweis: Prinzipiell finde ich das Kapitel mit dem Vergleich aus kaufmännischer Sicht nicht wirklich optimal. Zugegeben, mir ging es in erster Linie um das Prinzip in Sachen Power Query. 💡 Wenn Sie etwas experimentierfreudig sind dann tragen Sie doch in den Quelldaten der beiden Läger eine Spalte mit dem Mindestbestand ein. Da lässt sich dann wunderschön vergleichen, ob in einem Lager der Mindestbestand unterschritten ist und im anderen Lager der Mindestbestand überschritten ist. Da kann Power Query sogar berechnen, welche Menge (mindestens) transferiert werden kann oder soll; natürlich darf dabei der eigene MB nicht unterschritten werden. Mit PQ und etwas Formel-Kenntnissen kein Problem.
Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen … (← Klick mich!)