Entferne Zeilen, die in einer anderen Tabelle existieren, mit Power Query – Wie man einen Anti-Join (Not-In-Join) zur Datenbereinigung in Power BI verwendet

Wenn Sie Ihre Daten in Power Query aufbereiten, kann es vorkommen, dass Sie Zeilen in einer Tabelle entfernen müssen, die in einer anderen Tabelle vorhanden sind. Wenn man Daten in Power Query aufbereitet, kann es vorkommen, dass man in einer Tabelle Zeilen entfernen muss, die in einer anderen Tabelle vorhanden sind. Ein Beispiel wäre, wenn man in der Sales-Tabelle Zeilen von Geschäftskunden oder generell Zeilen aus einer Art “Blacklist” ausschließen möchte. In SQL nennt man dies einen Anti-Join.

Wie man in Power Query einen Anti-Join verwenden kann, erkläre ich in diesem Artikel.

Was ist ein Anti-Join oder ein Not-In-Join?

Ein Anti-Join oder auch Not-in-Join, ist eine Möglichkeit, Zeilen in einer Tabelle auszusortieren, die es bereits in einer anderen Tabelle gibt. Es ist sozusagen das Gegenteil einer regulären Joins, bei der man zusammengehörige Zeilen aus zwei Tabellen kombiniert.

Mit einem Anti-Join schließt man Zeilen aus einer Tabelle aus, die einen passenden Wert in einer anderen Tabelle haben, basierend auf einer bestimmten Spalte. Dies kann für Datenbereinigungsaufgaben nützlich sein, z. B. zum Entfernen von Duplikaten oder zum Herausfiltern unerwünschter Daten.

Szenario für unser Beispiel

In unserem Fall stellen wir uns vor, dass wir eine Faktentabelle mit mehreren Verkäufen haben. Wir wollen nur die Verkäufe an Endkunden analysieren, also müssen wir unsere Händler ausschließen, da sie ein falsches Ergebnis liefern würden. Dies, da ihr Verkaufsverhalten nicht dem eines typischen Endkunden entspricht.

So sieht unsere Sales-Tabelle aus:

Glücklicherweise haben wir bereits eine Liste unserer Geschäftskunden, die wir für unseren Anti-Join verwenden wollen. Man beachte, dass ein Eintrag doppelt vorhanden ist. Dies dient dazu zu prüfen, ob Power Query mit doppelten Zeilen umgehen kann. Aber dazu kommen wir später. Dies ist unsere “Blacklist” von Geschäftskunden, die wir aus unserer Faktentabelle ausschließen wollen:

Welche Joins gibt es in Power Query

Wie bereits erwähnt, können wir einen Join verwenden, um die Zeilen auszuschließen, die auf der Blacklist stehen. In Power Query wird ein Join als “Merge” bezeichnet. Dazu klickt man einfach auf der Registerkarte “Home” von Power Query auf “Merge Queries”:

Daraufhin wird ein Dialog ähnlich dem folgenden angezeigt:

Zunächst müssen wir wählen, mit welcher Tabelle wir die ausgewählte Tabelle zusammenführen wollen. In unserem Fall ist das die Blacklist-Tabelle “Companies”. Dann müssen wir die Spalten auswählen, die für den Join verwendet werden sollen. In unserem Fall ist es der CustomerKey aus der Tabelle Sales und die CustomerID aus der Tabelle Companies.

Wir können auch mehrere Spalten für einen Join verwenden. Dazu kann man die Strg-Taste gedrückt halten, während man die Spalten nach einander anklickt.

So erstellt man einen Anti-Join in Power Query

Und jetzt kommt der wichtige Schritt: In der Auswahlliste “Join Kind” gibt es verschiedene Arten von Joins, aus denen wir auswählen können. Da wir in der ersten Tabelle alles ausschließen wollen, was in der zweiten Tabelle vorhanden ist, wählen wir “Left Anti (rows only in first)“.

Nachdem wir mit OK bestätigt haben, werden alle Zeilen, die in der zweiten Tabelle existieren, aus der ersten Tabelle entfernt:

Wir haben eine zusätzliche Spalte mit der Bezeichnung “Companies”, die alle Zeilen des Joins enthalten würde. Da wir einen Anti-Join haben, werden sie allerdings alle leer sein, also können wir diese neue Spalte einfach löschen.

Zusammenfassung

Wir haben gesehen, wie einfach es ist, Zeilen zu entfernen, die in einer anderen Tabelle vorhanden sind und einen Anti-Join durchzuführen. Diese Aufgabe mag einfach erscheinen, aber ich habe in letzter Zeit einige Anfragen dazu erhalten, weshalb ich mich entschlossen habe, diesen Artikel zu schreiben.

Eine Sache, die ich nicht mehr erwähnt habe, ist die Frage nach der doppelten Zeile. Wie man sieht, wurden alle Zeilen für Unternehmen Nummer 3 fehlerfrei entfernt. Selbst wenn also die “schwarze Liste” mit einer SQL-Query erstellt wird und aufgrund eines sich ändernden Namens doppelte Zeilen entstehen, wird der Anti-Join nicht brechen und ist in der Lage, dies zu verarbeiten.

Download Beispieldatei: