Power Query-Joins mit Excel-Funktionen nachbauen (Teil 3)

Wie man die 6 verschiedenen Joins aus Power Query nur mit Excel-Funktionen nachbaut, habe ich dir ja in den letzten beiden Blogartikeln gezeigt.

Im heutigen letzten Beitrag meiner dreiteiligen Artikelserie setzen wir dem Ganzen noch die Krone auf:
Zuerst richten wir ein Dropdown-Feld ein, über das sich steuern lässt, welcher der 6 Joins angewendet werden soll.

Und schließlich werden wir noch eine eigenständige JOIN-Funktion erstellen, die den normalen Funktionsumfang von Excel um genau diese 6 Joins erweitern wird. Damit du in Zukunft nicht die komplizierten einzelnen Formeln nachbauen musst, sondern einfach die neue JOIN-Funktion in allen deinen Arbeitsmappen nutzen kannst. So, als ob sie eine Standardfunktion wäre.

Los geht’s!

Beispieldatei herunterladen

Noch eine wichtige Information zu Beginn:
Für die hier gezeigten Lösungen braucht du Excel 2024 oder Microsoft 365. In älteren Versionen stehen die für die Joins benötigten Funktionen leider nicht zur Verfügung.

Falls du die ersten beiden Artikel zu den Joins verpasst haben solltest, kannst du hier einsteigen:
Power Query-Joins mit Excel-Funktionen nachbauen (Teil 1)
Power Query-Joins mit Excel-Funktionen nachbauen (Teil 2)

Inhaltsverzeichnis

Join-Auswahl per Dropdown-Feld

Bisher haben wir ja für jeden einzelnen Join-Fall eine eigene Formel erstellt. Wäre es nicht schön, wenn man den gewünschten Join auswählen könnte und die Formel spuckt dann das passende Ergebnis aus?

Etwa so:

Die fertige Lösung mit Dropdown-Feld

Dazu habe ich in einem separaten Arbeitsblatt eine kleine Tabelle mit dem Namen t_Joins angelegt, die als Quelle für das Dropdown-Feld verwendet werden soll. Die erste Spalte enthält die Auswahlwerte, in die zweite, optionale Spalte habe ich noch das zum jeweiligen Join passende Bild gepackt.

Die Quelle für das Dropdown-Feld

Wie bekommt man die Bilder in die einzelnen Zellen?

Dazu benötigst du Excel aus Microsoft 365. Dort lässt sich über das Menü „Einfügen | Bilder | In Zelle platzieren…“ eine beliebige Bilddatei von deinem Computer oder aus einer Online-Quelle auswählen und direkt in eine Zelle einfügen. Genauso, wie eine Formel, Zahl oder ein Text. In der Bearbeitungszeile wird dann nur ein Platzhalter angezeigt:

Die eingefügten Bilder sind optional

Klickt man auf das kleine Bildsymbol, das rechts neben einer solchen Bilderzelle angezeigt wird, dann wird das Bild aus der Zelle herausgeholt und als normales Bildobjekt angezeigt. Und genauso lässt sich solch ein schwebendes Bild durch einen Mausklick wieder in eine Zelle packen:

Ein Bild-Objekt kann per Mausklick in eine Zelle gepackt werden (nur M365)

Aber das ist für unser Beispiel nur ein kleines optisches Schmankerl, das nicht zwingend notwendig ist. Wichtig hingegen ist, dass wir für die Spalte mit den Join-Arten einen eigenen Namen im Namensmanager festlegen. Ich verwende hier den Namen „dd_Join“:

Ein Name für die Quellspalte wird definiert

Das Dropdown-Feld wird dann wie üblich über das Menü „Daten | Datenüberprüfung“ eingerichtet:

Das Dropdown-Feld wird eingerichtet

Nun soll nach der Auswahl eines Joins daneben auch das passende Bild angezeigt werden. Dafür nutzen wir eine einfache XVERWEIS-Funktion:
=XVERWEIS(K2;t_Joins[JoinArt];t_Joins[Symbol];"")

Damit das Symbol auch ein bisschen größer und gut erkennbar angezeigt wird, habe ich die Zellen L2:M4 verbunden (ja, ich nutze hier wirklich den ansonsten gehasste Zellverbund!). Danach sollte das Ergebnis so aussehen:

Das Bild wird per XVERWEIS eingeblendet.

Die kombinierte Join-Formel

Fehlt nur noch die Formel, die den gewünschten Join dann ab Zelle J6 auch wirklich ausgibt. Und jetzt musst du leider ganz stark sein:
Da hier alle 6 einzelnen Formeln in einer großen zusammengefasst werden, wird diese Formel etwas länger…

Aber keine Angst, dank der LET-Funktion wird es so schlimm auch wieder nicht.

Im Wesentlichen wird innerhalb einer LET-Funktion für jeden Join ein Variablenname definiert und diesem dann die jeweilige Formel zugewiesen. Die einzelnen Formeln kennst du ja schon aus den ersten beiden Blogartikeln.

Für die ersten beiden Joins sieht das dann so aus. Damit man die einzelnen Bausteine besser lesen kann, habe ich mit Alt+Eingabe manuelle Umbrüche gesetzt und die Variablennamen fett gedruckt:
=LET( Linker_äußerer_Join;HSTAPELN( t_KundenProdukt1; WENNFEHLER(INDEX(t_KundenProdukt2;XVERGLEICH(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr]);SEQUENZ(1;SPALTEN(t_KundenProdukt2)));"")); Rechter_äußerer_Join;HSTAPELN( WENNFEHLER(INDEX(t_KundenProdukt1;XVERGLEICH(t_KundenProdukt2[Kdnr];t_KundenProdukt1[Kdnr]);SEQUENZ(1;SPALTEN(t_KundenProdukt1)));""); t_KundenProdukt2); ...

Und so wird die Formel Stück für Stück fortgesetzt, bis schließlich alle 6 Joins enthalten sind.
... Vollständiger_äußerer_Join;LET( Kundennummern;EINDEUTIG(VSTAPELN(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr])); HSTAPELN( WENNFEHLER(INDEX(t_KundenProdukt1;XVERGLEICH(Kundennummern;t_KundenProdukt1[Kdnr]);SEQUENZ(1;SPALTEN(t_KundenProdukt1)));""); WENNFEHLER(INDEX(t_KundenProdukt2;XVERGLEICH(Kundennummern;t_KundenProdukt2[Kdnr]);SEQUENZ(1;SPALTEN(t_KundenProdukt2)));""))); Innerer_Join;HSTAPELN( SORTIEREN(FILTER(t_KundenProdukt1;ZÄHLENWENN(t_KundenProdukt2[Kdnr];t_KundenProdukt1[Kdnr])>0;"")); SORTIEREN(FILTER(t_KundenProdukt2;ZÄHLENWENN(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr])>0;""))); Linker_Anti_Join;FILTER(t_KundenProdukt1;ZÄHLENWENN(t_KundenProdukt2[Kdnr];t_KundenProdukt1[Kdnr])=0;""); Rechter_Anti_Join;LET( Kunden2;FILTER(t_KundenProdukt2;ZÄHLENWENN(t_KundenProdukt1[Kdnr];t_KundenProdukt2[Kdnr])=0;""); Kunden1;WENN(SEQUENZ(ZEILEN(Kunden2);SPALTEN(t_KundenProdukt1));""); HSTAPELN(Kunden1;Kunden2)); ...

Jetzt fehlt nur noch eine entscheidende Sache. Das letzte Argument in einer LET-Funktion ist immer die Berechnung, also der Wert, der am Ende ausgegeben werden soll.

Die Berechnung soll ja abhängig vom gewählten Dropdown-Wert in Zelle K2 sein. Dafür nutze ich die Funktion ERSTERWERT.

Diese eher unbekanntere Funktion hilft dabei, verschachtelte WENN-Formelmonster zu vermeiden. Ich habe dazu mal einen eigenen Artikel geschrieben.

In unserem Fall wird mit ERSTERWERT der Wert aus K2 abgefragt und davon abhängig die jeweilige berechnete Variable aus der LET-Funktion ausgegeben:
... ERSTERWERT($K$2; "Linker äußerer Join";Linker_äußerer_Join; "Rechter äußerer Join";Rechter_äußerer_Join; "Vollständiger äußerer Join";Vollständiger_äußerer_Join; "Innerer Join";Innerer_Join; "Linker Anti-Join";Linker_Anti_Join; "Rechter Anti-Join";Rechter_Anti_Join; "-"))

Und ist unser komplettes Schmuckstück fertig:

Die komplette Formel für den universellen Join per Dropdown

Ja, ich weiß: Das sieht wirklich furchteinflößend aus. In der Beispieldatei, die du dir hoffentlich heruntergeladen hast, findest du das fertig Prachtstück und musst dir nicht die Finger bei der Eingabe abbrechen.

Die eigene JOIN-Funktion

Wenn du es bis hierher geschafft hast: Respekt und herzlichen Glückwunsch, du bist offensichtlich hart im Nehmen!

Daher kommt hier der Ausblick auf die Belohnung:
Dieses Formelmonster werden wir jetzt noch mit Hilfe von LAMBDA in eine eigene JOIN-Funktion umwandeln, so dass du danach die Joins mit der folgenden simplen Syntax nutzen kannst:
=JOIN(Tabelle1;Schlüssel1;Tabelle2;Schlüssel2;Join-Art)

So soll die eigene JOIN-Funktion aussehen

Halte also noch ein kleines bisschen durch!

Die LAMBDA-Funktion ermöglicht es, benutzerdefinierte Funktionen zu erstellen und diese unter einem eigenen Namen zu speichern und anzusprechen. Wie das genau funktioniert, habe ich in diesem Artikel schon einmal beschrieben. Dort erkläre ich auch, wie man mittels Lambda erstellte benutzerdefinierte Funktionen dauerhaft in allen Arbeitsmappen bereitstellt.

Die gute Nachricht:
Wir können das vorhin erstellte Formelmonster fast komplett für unsere LAMBDA übernehmen. Lediglich ein paar kleinere Anpassungen sind notwendig, welche die Formel hinter flexibler und sogar etwas einfacher machen.

Ich habe unsere bisherige Original-Formel (im Bild links) der fertigen Lambda-Formel (rechts) gegenübergestellt. Alle notwendigen Anpassungen sind rot markiert:

Gegenüberstellung: Bisherige Formel versus LAMBDA

Dazu noch folgende Erläuterungen:
Gleich zu Beginn der LAMBDA-Funktion werden die Namen der 4 notwendigen Parameter definiert: Tabelle1, Schlüssel1, Tabelle2, Schlüssel2 und schließlich JoinArt.

Diese Parameter werden dann im weiteren Verlauf der Formel an den Stellen eingesetzt, wo in der Original-Formel fixe Tabellenbezüge enthalten waren. So wird aus „t_KundenProdukt1“ jetzt „Tabelle1“, aus „t_KundenProdukt1[Kdnr]“ wird „Schlüssel1“ und so weiter.

Dadurch lässt sich die Formel zukünftig auf beliebige Quelltabellen anwenden, es müssen nur die relevanten Bereiche markiert werden.

Und noch eine kleine Änderung gibt es in der ERSTERWERT-Funktion. Da man in der neuen JOIN-Funktion zukünftig die Join-Art als Parameter eintippen muss, habe ich die Bezeichnungen etwas verkürzt („Linker“ statt „Linker äußerer Join“) und stelle mit Hilfe der GROSS2-Funktion sicher, dass auch die Schreibweise bei der Eingabe nicht zu einem Problem wird.

Jetzt kommt der allerletzte und entscheidende Schritt!

Wir legen im Namensmanager einen neuen Namen „JOIN“ an und kopieren die komplette LAMBDA-Funktion in das Feld „Bezieht sich auf“. Zusätzlich geben wir in das Kommentarfeld noch eine kleine Erläuterung zur Funktionsweise, die dem Anwender bei der Eingabe der Formel angezeigt wird:

Die Lambda-Funktion wird in den Namensmanager gepackt

Fertig!

Jetzt kannst du in einer leeren Zelle die neue Funktion eintippen, natürlich beginnend mit einem Gleichheitszeichen. Nach den ersten beiden Zeichen wird sie dir auch schon angeboten und der Kommentar aus dem Namensmanager als Intellisense-Hilfe eingeblendet:

Anzeige des Kommentars bei der Formeleingabe

Sobald der ganze Funktionsname übernommen und die öffnende Klammer eingetippt wurde, werden die benötigten Parameter angezeigt:

Anzeige der benötigten Parameter bei der Formeleingabe

Und jetzt kannst du einfach wie bei den Standardfunktionen einfach die entsprechenden Quellbereiche mit der Maus oder Tastatur markieren und die gewünschte Join-Art eintippen (in doppelten Anführungszeichen). Und schon erhältst dann das entsprechende Resultat:

Die fertige JOIN-Funktion im Einsatz

Fazit

Ja, das war ein harter Brocken. Aber ich denke, das Ergebnis kann sich sehen lassen, oder?

Mit etwas Lust zum Basteln lassen sich die Joins aus Power Query durchaus mit reinen Excel-Funktionen nachbauen. Der Vorteil dieser Variante gegenüber Power Query ist, dass sich die Ausgaben einer Formel sofort aktualisieren, sobald sich an der Datenquelle etwas geändert hat. Mit Power Query muss dazu erst eine Aktualisierung angestoßen werden.

Aber natürlich darf an dieser Stelle auch nicht verschwiegen werden, dass die Formellösung nicht in jedem Szenario optimal ist. Sobald es um große Datenmengen geht oder die Daten erst aus externen Quellen geladen werden müssen, führt um Power Query kein Weg herum.

Die hier vorgestellten Lösungen sollen einfach eine mögliche Alternative aufzeigen und im besten Fall die Lust am Probieren und Tüfteln wecken. Und vielleicht bist du ja auch auf den Geschmack gekommen, um LET und LAMBDA zukünftig häufiger einzusetzen.