SVerweis mit Power Query

Aufrufe: 28858

Wer kennt ihn nicht, den SVerweis? Viele verzweifeln an ihm, andere setzen ihn virtuos ein. Aber der SVerweis hat einige Nachteile. In diesem Beitrag zeige ich Euch, wie ihr den SVerweis mit Power Query ganz einfach nachbildet und die Limitationen des SVerweises beseitigen könnt.

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

Nachteile des SVerweis

Es gibt viele Vor- und Nachteile der Funktion SVerweis. Jedoch überwiegen die Nachteile. Ich nenne hier nur drei:

• Der SVerweis holt immer den ersten übereinstimmenden Wert aus einer anderen Tabelle. Weitere Werte werden nicht geholt.
• Wenn sich die Spaltenanordnung in der anderen Tabelle verändert, funktioniert der SVerweis nicht mehr.
• Bei grossen Tabellen wird der SVerweis sehr langsam, da bei jeder Abfrage die ganze Tabelle von neuem durchsucht wird.

Ich habe ein kleines Beispiel zum ersten Punkt vorbereitet. Es besteht aus zwei Ausgangstabelle “Kunden” und “Aufträge”.

Ziel ist es, in Excel mit SVerweis eine dritte Tabelle zu erstellen, die aus den zwei Ausgangstabellen zusammengesetzt ist. Das Resultat sieht, nicht überraschend, wie folgt aus:

Zwei Dinge fallen sofort auf:

1. wird nur ein Wert pro Kunde geholt
2. Der Kunde Schneider hat noch keine Aufträge platziert und deshalb erscheinen Fehlermeldungen (#NV).

SVerweis mit Power Query

Diese Limitationen des SVerweises löst Ihr dank Power Query ganz einfach.

Importiert als erstes die beiden Tabellen je über den folgenden Menüpunkt:

Die Daten sind bereits im korrekten Format importiert. Als nächstes definiert Ihr für beide Tabellen, wohin sie geladen werden.
Über “Schliessen & laden in” und danach “Nur Verbindung erstellen” wird definiert, dass die Daten nicht nochmals in Excel importiert werden.

Tabellen zusammenführen

Wählt als nächstes die importierte Tabelle “Kunden” aus und erstellt einen neuen Schritt über “Abfragen zusammenführen” und “Abfragen als neue Abfrage zusammenführen”.

Dieser Befehl ergänzt die bisherige Tabelle um neue Spalten. Mit “Abfragen als neue Abfrage zusammenführen” erzeugt Ihr eine neue Tabelle. Im entsprechenden Dialog sind die beiden Tabellen auszuwählen. Legt danach fest, über welche Spalten das Zusammenführen geschieht. Ihr könnt auch mehrere Spalten auswählen. Verbindet beiden Tabellen “Tb_Kunden” und “Tb_Aufträge” über die Kundennummer. Es sollen alle Werte aus der ersten Tabelle und aus der zweiten Tabelle nur die entsprechenden Werte hinzugefügt werden. Dies ist ein sogenannter Linker äusserer Join in der Datenbanksprache. Weitere Erläuterungen dazu findet Ihr hier: sieben Arten der Tabellenzusammenführung

Als nächstes klickt Ihr auf die beiden Pfeile in der neuen Spalte “NewColumn”. Im folgenden Dialog sind die gewünschten Spalten auszuwählen und zu definieren, ob wir die ursprünglichen Spaltennamen als Präfix behalten wollen. Deaktiviert dies.

Und schon haben wir die gewünschte Tabelle:

Im Gegensatz zum SVerweis zeigt Power Query jeden Auftrag pro Kunden an und beim Kunden Schneider erscheint anstelle eines Fehlerwertes der Wert null.
Die Tabelle wird noch in “SVerweis mit PowerQuery” umbenannt und danach nach Excel als neues Arbeitsblatt exportiert.

Wiederverwenden der Schritte

Wenn Ihr in Excel neue Werte in die beiden Ursprungstabellen eingebt und die Power Query Abfrage aktualisiert, so berücksichtigt die Abfrage sämtliche Mutationen und die Tabelle “SVerweis mit Power Query” ist mit den neuen Werten ergänzt.

Der SVerweis in Excel kann mit solchen Änderungen ohne manuellen Eingriff nicht umgehen.

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

Happy Querying!