This post is also available in:

Aufrufe: 57901

Dieser Artikel “sieben Arten der Tabellenzusammenführung” ist in Vorbereitung auf eine Benutzerschulung entstanden und ich möchte ihn Euch nicht vorenthalten. Ganz am Schluss zeige ich auch noch zwei Wege für das Umgehen der Gross-/Kleinschreibung auf.

Willst du Power BI lernen?
Auf unseren neuen Seite Power BI Coach haben wir einen Onlinekurs zusammengestellt.

 

Inhaltsverzeichnis

1. Welche Arten gibt es?
2. Auf was muss geachtet werden?
3. Wie wird zusammengeführt?
4. Beispiele
5. Fortgeschrittenes Beispiel –
   Zusammenführung von Text unabhängig von Gross-/Kleinschreibung
6. Spezialfall – Cross Join

1. Welche Arten gibt es?

Tabellen, die über Spalten mit den gleichen Inhalten verfügen, können problemlos und einfach über die Benutzeroberfläche zusammengeführt werden. Dabei können auch mehrere oder nur eine einzige Spalte als Zusammenführungskriterium definiert werden.
Es stehen insgesamt sechs Arten der Tabellenzusammenführung über die Benutzeroberfläche zur Verfügung.

• Linker äusserer Join
• Rechter äusserer Join
• Vollständiger äusserer Join
• Innerer Join
• Linker Anti-Join
• Rechter-Anti Join

Grafische Darstellung der Join-Arten:

2. Auf was muss geachtet werden?

Jede der zusammenzuführenden Tabellen muss über die gleichen Spalten verfügen, wobei diese auch unterschiedlich benennt sein können. Die Datentypen müssen hingegen zwingend übereinstimmen. Dabei gilt zu beachten, dass Spalten mit dem Datentyp Ganzzahl performanter zusammengeführt werden als Spalten im Datentyp Text. Falls trotzdem, und im Ausnahmefall, die Zusammenführung über Textspalten geschehen soll, so ist zusätzlich auf die Gross-/Kleinschreibung zu achten. Zum Beispiel erkennt Power Query die Wörter “Haus” und “haus” nicht als gleiches Wort.
Als erste Tabelle wird jeweils die Faktentabelle (die mit den Transaktionen) und als zweite die Dimensionstabelle ausgewählt. Beim Zusammenführen von reinen Dimensionstabellen (zum Beispiel “Produkte” und “Produktgruppen”) wird als erstes die detailliertere Dimension gewählt.
Sollte aus dem Zusammenführen unbeabsichtigt eine grössere Anzahl Zeilen als vor dem Zusammenführen resultieren, so ist zu prüfen, ob die richtige Join-Art gewählt wurde und allenfalls anzupassen.

3. Wie wird zusammengeführt?

Das Zusammenführen geschieht über die Benutzeroberfläche im Menüpunkt “Start” unter “Kombinieren“.

Das Resultat kann entweder in eine neue Tabelle (“Abfrage als neue Abfrage zusammenführen”) oder in der ersten Tabelle (“Abfrage zusammenführen”) ausgegeben werden.

4. Beispiele

Die Faktentabelle A umfasst verschiedene Transaktionen. In der Dimensionstabelle B sind die Lagerorte der Produkte aufgeführt. Da beide Tabellen je die Spalte “ProductID” im Datentyp “Ganzzahl” aufweisen, können wir sie über diese Spalte zusammenführen. Die beiden Tabellen sind in allen sechs möglichen Arten zusammenzuführen. Importiert als erstes die Daten der beiden Tabellen in Power Query und benennt die Abfragen in “Tabelle A” respektive “Tabelle B” um.

4.1 Linker äusserer Join

Zweck
Der linke, äussere Join gibt alle Zeilen aus der ersten Tabelle zurück und sucht sich aus der zweiten Tabelle die übereinstimmenden Zeilen heraus. Wir können definieren, welche Spalte zurückgegeben werden sollen.
Diese Funktion entspricht in Excel dem Befehl “SVerweis“. Im Gegensatz zum SVerweis werden aber alle übereinstimmenden Zeilen zurückgeben, während der SVerweis jeweils nur den ersten Treffer zurückgibt.

Vorgehen
Als erstes sind die beiden Tabellen mittels Power Query zu laden. Danach führen wir sie als neue Abfrage zusammen.

Im nun geöffneten Dialog markieren wir in beiden Tabellen je die Spalte “ProductID” und wählen als Join-Art “Linker äusserer Join”.

Als nächstes ist die neue Spalte wie gewohnt mit Klick auf das Symbol zu erweitern und die zurückzugebenden Spalte auszuwählen. Ich habe alle Spalten markiert. Bei Bedarf kann auch noch ein Spaltenpräfix definiert werden.

Resultat
Das Resultat habe ich in Excel geladen, um es zu visualisieren.
Es wurden wie gewünscht alle Produkte der Tabelle A mit den übereinstimmenden Spalten aus der Tabelle B ergänzt. Wo keine Übereinstimmung vorhanden ist, werden leere Felder zurückgegeben.

4.2 Rechter, äusserer Join

Zweck
Der rechte, äussere Join gibt alle Zeilen aus der zweiten Tabelle zurück und sucht sich aus der ersten Tabelle die übereinstimmenden Zeilen heraus. Also genau das Gegenteil der ersten Variante.

Vorgehen
Das Vorgehen ist genau gleich und ändert sich auch in den anderen Beispielen nicht mehr. Einzig die Join-Art ist zu ändern.

Resultat
Das Resultat sieht wie folgt aus und enthält alle Produkte aus der Tabelle B sowie die übereinstimmenden Spalten aus der Tabelle A:

4.3 Vollständiger äusserer Join

Zweck
Der vollständig äussere Join holt alle Zeilen aus beiden Tabellen.

Vorgehen
Diese Join-Art ist der dritte Punkt im Dropdown Menü.

Resultat

Als Resultat haben wir eine aus den Tabellen A und B kombinierte Tabelle mit allen Produkten zurückerhalten.

4.4 Innerer Join

Zweck
Im Gegensatz zum äusseren Join gibt diese Join-Art nur die übereinstimmenden Zeilen zurück.

Vorgehen
Diese Join-Art ist der vierte Punkt im Dropdown Menü.

Resultat

Das Resultat umfasst nur Produkte, die in beiden Tabellen enthalten sind.

4.5 Linker Anti Join

Zweck
Diese Join-Art gibt diejenigen Zeilen zurück, welche nur in der ersten Tabelle enthalten sind.

Vorgehen
Diese Join-Art ist der fünfte Punkt im Dropdown Menü.

Resultat

Das Resultat zeigt, welche Produkte nur in Tabelle A, aber nicht in Tabelle B enthalten sind.

4.6 Rechter Anti Join

Zweck
Diese Join-Art gibt nur diejenigen Zeilen zurück, welche nur in der zweiten Tabelle enthalten sind.

Vorgehen
Diese Join-Art ist der sechste Punkt im Dropdown Menü.

Resultat
Im Gegensatz zum linken Anti-Join sehen wir hier diejenigen Produkte, welche nur in Tabelle B, aber nicht in der Tabelle A enthalten sind.

4.7 Zusammenfassung – sechs Arten der Tabellenzusammenführung

Ich stelle die vorhandenen sechs Arten der Tabellenzusammenführung nochmals einander gegenüber.

5. Fortgeschrittenes Beispiel – Zusammenführung von Text unabhängig von Gross-/Kleinschreibung

Die vorstehenden Beispiele haben wir ausschliesslich über die Benutzeroberfläche und unter Verwendung von Spalten mit dem Datentyp “Ganzzahl” ausgeführt. Wie eingangs erwähnt, ist es möglich, auch Textspalten unter Beachtung der Gross-/Kleinschreibung zusammenzuführen.
Als Datenbasis dienen die beiden nachstehenden Tabellen:

Es fällt sofort auf, dass die Schreibweisen der Namen völlig unterschiedlich sind. Das Resultat eines normalen Joins (linker äusserer Join) ergibt folgendes Resultat:

Bei einem einzigen Namen wurde eine Übereinstimmung gefunden. Die restlichen Wörter hat Power Query aufgrund der unterschiedlichen Schreibweise als nicht übereinstimmend eingestuft.

Das gewünschte Resultat sollte aber so aussehen:

1. Lösungsansatz: Neue Spalten mit Grossschreibung

Ein einfacher Ansatz kann sein, dass in beiden Tabellen je eine neue Spalte hinzugefügt wird, welche die Namen in Grossschreibung umwandeln.

Natürlich müssen die beiden neuen Spalten danach wieder gelöscht werden.

2. Lösungsansatz: “M” Code schreiben

Mittels nachstehendem Code kann die Gross-/Kleinschreibung ebenfalls umgangen werden. Aber gleich ein Wort der Warnung – bei grossen Datenmengen wird die Codeausführung langsam sein!

Statt einem Join werden die Daten aus der zweiten Tabelle als neue Spalte namens “Merge” hinzugefügt (Table.AddColumn). Die Verknüpfung erfolgt über die Tabelle “Trans”.
Die Funktion (case_insensitive) selektiert alle Zeilen aus der Tabelle “Dept” und vergleicht diese mit den Zeilen aus der ersten Tabelle. Der Vergleich erfolgt, indem die Schreibweise sämtlicher Wörter in den beiden Spalten (“Name”) auf Gross geändert wird (Text.Upper).
Zu guter Letzt muss die neue Spalte “Merge” noch erweitert werden. Da es sich um eine rekursive Funktion handelt, empfehle ich die Anwendung aber nur in kleinen Datenmengen!

6. Spezialfall – Cross Join

Der Cross Join ist ein Spezialfall. Er bildet aus zwei Tabellen ein kartesisches Produkt und es gibt keine Spalten, die als Join Bedingungen dienen. Es werden jeweils alle Zeilen der linken Tabelle mit allen Zeilen der rechten Tabelle zusammengeführt.

Für dieses Beispiel habe ich zwei neue Tabellen erstellt:

Der Cross Join wird in Power Query mittels einer neu hinzuzufügenden Spalte initiiert. Im Benutzerdialog muss lediglich auf die andere Tabelle verwiesen werden.

Als zweiter Schritt wird die Spalte erweitert und wir erhalten folgende Tabelle:

Die 3 Werte der ersten Tabelle werden mit den 3 Werte der zweiten Tabelle kombiniert. Es resultieren 9 Zeilen, welche alle möglichen Kombination darstellen.
Diese Join Art kann bei grossen Tabellen sehr schnell zu riesigen Ergebnissen führen. Die Durchführung des Cross Joins kann sehr zeitaufwändig werden und sollte deshalb nur in Ausnahmefällen verwendet werden.

Liebe Leser, ich schreibe meine Artikel für Euch. Es ist mein Bestreben, Euch qualitativ ansprechenden Inhalt zu liefern. Nehmt Euch bitte eine Minute Zeit und gebt mir nachstehend eine Bewertung ab oder schreibt einen Kommentar. Besten Dank.

Mehr Artikel über Power Query findet Ihr im Power Query Online Buch.

Happy Querying!