Office-Blog

Im Beitrag vom 3. Juli 2018 habe ich beschrieben, wie Listen mit gleichen Spalten über Joins miteinander abgeglichen werden. Heute geht es um Listen, die keine Gemeinsamkeit aufweisen.

Hier im Beispiel sind es Artikel, die in unterschiedlichen Farben erhältlich sind. Je nach Farbe gibt es für jeden Artikel auf den Basispreis einen prozentualen Aufschlag. Gebraucht wird also eine Artikelliste, die für jeden Artikel in jeder Farbkombination den Preis bereithält.

Per Cross Join zwei Listen kombinieren

Das lässt sich lösen mit einem speziellen Join – dem sog. Cross Join (Kreuzprodukt). Er kombiniert alle Daten einer Tabelle mit allen Daten einer anderen Tabelle.

Allerdings ist der Cross-Join in Power Query nicht über Start > Kombinieren > Abfragen als neue Abfrage zusammenführen zu finden. Er muss über eine benutzerdefinierte Spalte herbeigeführt werden.

Mit diesen Schritten wird ein Cross Join realisiert

• Zunächst wird in Excel über Daten > Aus Tabelle/Bereich die Tabelle mit den Farben und anschließend die Tabelle mit den Artikeln in Power Query geladen. Die Abfragen erhalten die Namen Farben beziehungsweise Artikel.
• Im Power Query-Editor wird die Abfrage Artikel [1] über das Register Spalte hinzufügen [2] > Benutzerdefinierte Spalte [3] erweitert.
• Im folgenden Dialog Benutzerdefinierte Spalte wird ein beliebiger Name [4] für die Spalte vergeben und als Formel ein Verweis auf die Tabelle Farben erstellt [5] mit der Formel =Farben.

Die Schritte, um per Cross Join zwei Listen zu kombinieren

• Nach dem Klick auf OK [6] erscheint die neue Spalte (im Bild rechts oben zu sehen).
• Diese Spalte wird erweitert per Klick auf die Pfeilschaltfläche [7] rechts in der Spaltenüberschrift Neu.
• Anschließend werden die gewünschten Spalten und deren Benennung ausgewählt [8] und mit OK [9] bestätigt.

Die Preise berechnen

• Jetzt wird der Preis jedes Artikels in den jeweiligen Farben berechnet. Dazu wird über das Register Spalte hinzufügen > Benutzerdefinierte Spalte eine neue Spalte Preis hinzugefügt. Im Dialog Benutzerdefinierte Spalte wird folgende Formel eingegeben: =[Basispreis]+[Basispreis]*[Zuschlag].
• Spalten, die in der endgültigen Liste überflüssig sind, werden per Rechtsklick in der jeweiligen Spalte und den Befehl Entfernen eliminiert.

Fazit

Durch das Erweitern der Tabelle Artikel um die Spalten der Tabelle Farben wird jeder Artikel mit jeder Farbe kombiniert.
Aus 5 Artikeln in 3 verschiedenen Farben entstehen also 5 x 3 = 15 Artikelpreise.

Die Wirkung des Cross Joins