Excel-Listen abgleichen und verknüpfen geht ganz leicht: Die 6 magischen Joins von Power Query

Was Datenbanken als Standard können, was sich in Excel mit mehr oder weniger Mühe per SVERWEIS und Zusammenkopieren erledigen lässt, lässt sich in Power Query mit ein paar Mausklicks und ohne jede Formel realisieren.

Welche Produkte für Kunde A werden auch an Kunde B verkauft?
Welche Artikel aus Lager A kann auch Lager B liefern?
Welche Artikel wurden im aktuellen Jahr nicht verkauft?
Welche Kunden haben im letzten keine Bestellung aufgeben?

Um solche Fragen zu beantworten, müssen Listen abgeglichen werden. Das erledigen in Power Query sogenannte Joins. Sie stellen die notwendigen Beziehungen zwischen den zu vergleichenden Tabellen her. So werden beispielsweise die Artikelnummern aus Lager A mit den Artikelnummern aus Lager B abgeglichen. Für diesen Abgleich stellt Power Query sechs verschiedene Join-Möglichkeiten zur Verfügung.

Im folgenden Beispiel geht es um den Verkauf von Artikeln in Europa und Asien. Die Artikel, die in beiden Kontinenten verkauft wurden, sind im Bild 1 fett dargestellt.

Bild 1: Welche Artikel wurden sowohl in Europa als auch in Asien verkauft? Sie sind fett hervorgehoben.

Mit diesen Schritten wird ein Join definiert

• In Excel über das Register Daten > Aus Tabelle/Bereich die beiden Umsatztabellen für Europa und Asien in Power Query laden
• Im Power Query-Editor über das Register Start > Kombinieren [1] > Abfragen als neue Abfrage zusammenführen [2]
• Im Dialogfenster Zusammenführen die Tabellen Europa [3] und Asien [4] auswählen
• In jeder dann angezeigten Tabelle die Spalte Artikel als übereinstimmende Spalte markieren [5] [6]
• Unter Join-Art je nach Fragestellung die passende Auswahl – wie weiter unten beschrieben – treffen [7]

Bild 2: Über den Befehl Kombinieren > Abfragen zusammenführen stehen 6 verschiedene Joins zur Auswahl

Hinweis:

• Die übereinstimmenden Spalten müssen nicht den gleichen Namen tragen, wohl aber vom gleichen Datentyp sein.
• Durch Drücken der Strg-Taste beim Markieren können auch mehrere Spalten zum Abgleich gewählt werden. Diese werden dann mit Nummern für die eindeutige Zuordnung der Spalten versehen.

Nach Auswahl der Join-Art können über die neue Spalte Asien in Bild 3 [1] [2] die gewünschten Spalten aus der Asien-Tabelle gewählt werden.

Bild 3: Nach Auswahl des passenden Joins können die gewünschten Spalten ausgewählt werden

6 Fragen … 6 Joins … 6 Antworten

Nachfolgend wird der Nutzen der verschiedenen Joins dargestellt.

Frage 1: Welche in Europa verkauften Artikel wurden auch in Asien abgesetzt und mit welchem Umsatz? Dabei kommt der linke äußere Join zum Einsatz.

 

Frage 2: Welche in Asien verkauften Artikel wurden auch in Europa abgesetzt und mit welchem Umsatz? Dabei kommt der rechte äußere Join zum Einsatz.

 

Frage 3: Welcher Umsatz wurde auf beiden Kontinenten mit allen Artikeln generiert?
Hier liefert der vollständige äußere Join die nachfolgende Gesamtliste.

 

Frage 4: Welche Artikel wurden auf beiden Kontinenten, also sowohl in Europa als auch in Asien, verkauft und mit welchem Umsatz?
Der innere Join liefert die sog. Schnittmenge der auf beiden Kontinenten verkauften Artikel

 

Frage 5: Welche Artikel wurden nur in Europa verkauft und mit welchem Umsatz?
Der linke Anti Join liefert das Ergebnis. Die Spalten für Asien wurden im Bild nur zur Verdeutlichung mit aufgeführt.

 

Frage 6: Welche Artikel wurden nur in Asien verkauft und mit welchem Umsatz?
Entsprechend Frage 5 kommt hier der rechte Anti Join zum Einsatz. Auch hier wurden die Artikel und Umsatzspalte aus Europa nur zur Verdeutlichung belassen.

 

FAZIT: Die obigen Fragestellungen in Excel mit Formeln und Kopieren zu lösen, bedarf einigen Aufwands. In Power Query hingegen lässt sich das in Sekunden durch Klicken erledigen.

Lesen Sie im Folgebeitrag, wie mit einem Spezialjoin – dem sog. Cross Join (Kreuzprodukt) – alle Daten der einen Tabelle mit allen Daten der anderen Tabelle kombiniert werden können.