$ Gruppieren und verketten mit Power Query

Xtract: In einer (ungeordnten) 2-spaltigen Liste sind verschiedene Lieferanten mit jeweils 1 lieferbaren Produkt aufgeführt. Mehrere Produkte = mehrere Zeilen. Als Ziel sollen je 1 Zeile für Lieferanten und alle Produkte der jeweiligen Lieferanten in 1 Zelle aufgeführt werden.

  Wissensstand: Level 3 ⇒  Excel GUT!, mindestens Basis-Kenntnisse in Power Query   

PQ: Unterschiedliche Wege, Daten zu gruppieren und Werte der Zeilen in 1 Feld zu kombinieren

Am Beispiel einer Auflistung mit Produkten und verschiedener Lieferanten dieser Artikel zeige ich Ihnen in diesem Beitrag einen Weg auf, je Lieferant die entsprechenden Produkte in 1 Zelle, hier jeweils durch einen Separator getrennt, darzustellen. Laden Sie die Daten gerne hier herunter.

Hinweise zu den Roh-Daten

Das Blatt Obstsorten bedarf prinzipiell keiner Erläuterung. Produktnummer und Produktbezeichnung, alles IT-gerecht. Zu Lieferanten ist anzumerken, dass ich einfach (mit Ausnahme der ersten Zeile) aus Gründen der Neutralität die Namen aller Bundeskanzler der Bundesrepublik Deutschland bis heute (März 2022) verwendet habe. Da sollte jedem klar sein, dass zufällige Namensübereinstimmungen mit existierenden Unternehmen offensichtlich nichts mit der Realität zu tun haben. 😉

Und das Blatt Rohdaten enthält ganz bewusst nur die numerischen Werte für die Lieferanten als auch die Obstsorten. Ich nutze die Gelegenheit, den PQ-Verweis sinnvoll einzusetzen. Allerdings tanzt die Zeile 52 etwas aus der Reihe. Einerseits gehört sie nicht zur Intelligenten Tabelle und andererseits steht in A52 und B52 kein fester Wert sondern jeweils eine Formel. Diese kann ich nämlich gut nutzen, wenn ich einmal andere Zufallszahlen im Bereich der Liste darüber generieren und die Formel nicht neu schreiben möchte. (Das habe ich beispielsweise genutzt, als ich den nun neuen Bundeskanzler Scholz hinzugefügt habe.) Und beim Import in Power Query werden natürlich nur die Werte der Liste übernommen. 💡

▲ nach oben …

Zielsetzung

Das Ziel dieser Übung ist, eine Tabelle mit der Aussage „wer liefert welches Obst“ zu erstellen. In Spalte A sollen die Namen aller 10 Lieferanten stehen und in Spalte B jeweils in 1 Zelle zusammengefasst all jene Obstsorten, die der jeweilige Lieferant im Programm hat. Die Produkte sollen als Separator ein Komma mit nachfolgendem Leerzeichen enthalten. (Weiter unten finden Sie das Wunschergebnis als Screenshot.)

Ich stelle Ihnen im erweiterten $$-Beitrag 2 Wege vor, wie sie in Power Query realisierbar sind. In plain Excel gibt es zwar je nach Version unterschiedliche Wege, die Texte zu verketten, das soll hier aber nicht diskutiert werden. Und Power Query ist hier ausnahmsweise einmal in Sachen Komfort im Hintertreffen. Sprich: PQ ist für diese Zielsetzung doch aufwendiger zu handhaben, für größere Datenmengen jedoch ganz klar im Vorteil.

▲ nach oben …

Vorarbeiten

Ich gehe davon aus, dass Sie dieses File von unserem Server heruntergeladen haben. Nacheinander importieren Sie nun die Tabellen von den Arbeitsblättern Obstsorten, Lieferanten und Rohdaten. Da ich den Tabellen bereits „sprechende“ Namen gegeben habe, sind die Bezeichnungen der einzelnen Abfragen aussagekräftig und brauchen nicht geändert zu werden. Idealerweise werden Sie die einzelnen Abfragen nach dem Import sofort über Schließen & laden in… | Nur Verbindung erstellen sichern.

Öffnen Sie nun die Abfrage RawData und sorgen Sie im ersten Schritt dafür, dass die beiden Spaltennamen (Überschriften) jeweils durch beispielsweise ein #-Zeichen ergänzt werden. Ich klicke dazu in die Überschrift, F2, Ende und gebe dann das Zeichen # über die Tastatur ein. Der Hintergrund dieser Übung ist, dass nun über den PQ-Verweis die alphanumerischen, lesbaren Namen der Lieferanten und Obstsorten hinzugefügt werden, den alten Namen (ohne das #-Zeichen) bekommen sollen und dadurch natürlich nicht zwei Spalten mit identischem Namen existieren werden (was ja bekanntlich auch nicht möglich ist). Und zugegeben, mit genügend Erfahrung können Sie natürlich auch auf diesen Schritt verzichten und PQ die erfordelichen Anpassungen überlassen.

Gehen Sie nun über Kombinieren | Abfragen zusammenführen | Abfragen als neue Abfrage zusammenführen, um die Abfrage RawData im ursprünglichen Zustand zu belassen und anschließend in diesem Duplikat im ersten Schritt die der Lieferantennummer zugeordneten Firmennamen zu verknüpfen. Sie werden also die Abfrage Lieferanten als zweite Abfrage einsetzen. Nach dem schlißen des Dialogs  brauchen Sie beim Erweitern nur die Spalte Lieferanten einzufügen. Wenn ihnen dieses Vorgehen per PQ-Verweis nicht ganz so geläufig ist, können Sie die detaillierte Vorgehensweise hier in unserem Blog nachlesen.

Ergänzen Sie nun auf die prinzipiell gleiche Weise die Bezeichnung der Obstsorten, nur dass Sie nun die vorhandene, gerade eben erstellte Abfrage Zusamenfühen1 als Basis (Datenquelle) verwenden und keine neue Abfrage erstellen. Anschließend positionieren, verschieben Sie die beiden neu erstellten Spalten an die Position direkt nach dem dazugehörigen numerischen Wert. Der Optik wegen können Sie die automatisch angefügte Ergänzung .1 bei den Lieferanten.1 löschen oder gleich nur Lieferant als Spaltenüberschrift stehen lassen; und die Überschrift Bezeichnung würde ich in Obstsorte ändern. Auch diese Abfrage sollten Sie über Schließen & laden in… | Nur Verbindung erstellen sichern, damit PQ kein neues, eigentlich unnötiges Arbeitsblatt anlegt. 🙂 Ob Sie den Namen dieser Query ändern, überlasse ich Ihrem Geschmack; ich finde diesen ganz aussagekräftig und belasse es dabei.

Damit haben Sie eine solide Basis für die kommenden Schritte. Die Vorarbeit ist getan. Hier schon einmal das Ergebnis, welches sich am Ende Ihrer Bemühungen beispielsweise so zeigen soll; es stellt sich in der Excel-Umgebung so dar:

Beispielhaftes Teil-Excel-Wunschergebnis, alle lieferbaren Obstsorten in einer Zelle

Die Lieferantennummer und auch die Produktnummer der Obstsorten sind zur besseren Separierung in runde Klammern eingefasst. Um ähnliche Namen für Lieferanten und/oder Produkte besser unterscheidbar zu machen, wurde die modifizierte Lieferanten-bzw. Produktnummer mit den alphanumerischen Daten verknüpft. Die lieferbaren Obstsorten sind für jeden Lieferanten der Aufgabenstellung entsprechend alle in 1 Zelle jeweils durch ein Komma mit angehängten Leerzeichen zusammengefasst. Sie können das natürlich nach Ihrem eigenen Geschmack handhaben. 😎 Aber gleich dazu mehr… Ein präventiever Hinweis: Fällt Ihnen in Zeile 4 etwas auf? An passender Stelle kann das Entfernen von Duplikaten hilfreich sein, aber das überlasse ich Ihrem Erfahrungsschatz. 😉 

Und noch ein Hinweis: Den bis hierher erarbeiteten Stand der Dinge brauchen Sie auch als Basis für jene Lösung, welche ich Ihnen am Schluss dieses Beitrages anbiete.

▲ nach oben …

Der Lösungsweg

Die hier vorgestellte Lösung der Aufgabe ist so konzipiert, dass Sie (mit einer einzigen Ausnahme) ausschließlich über die GUI (die grafische Oberfläche), also mit diversen Mausklicks und ab und zu einer Eingabe über die Tastatur auskommen. Und wie auch schon im Vorbereitungs-Teil hierüber habe ich ganz bewusst den einen oder anderen Schritt mehr eingefügt, um Ihnen das Verständnis für die einzelnen Schritte zu erleichtern. Und ich versichere Ihnen, dass Sie später, wenn Sie diese Übung das eine oder andere Mal mit Ihren Daten durchexerziert haben, kürzere Wege mit weniger Schritten finden werden. Und alternativ bleibt Ihnen noch die ganz am Ende dieses Beitrages beschriebene Möglichkeit. 💡

Ich weiß, es sind viele Schritte. Je mehr Lieferanten, umso mehr Zwischenschritte. Darum auch schon an dieser Stelle mein Ratschlag, dass Sie sich gut überlegen, ob sich der erforderliche Aufwand (dieses Weges) bei mehr als 10, höchstens 15 Lieferanten lohnt. Aber sehen und entscheiden Sie selbst! Und wie bereits ganz zu Beginn geschrieben, in den neueren Vesionen des Excel gibt es teilweise besser überschaubarere Wege. Aber dann spielt Power Query eben keine Rolle… 🙄 

Die zuletzt erstellte Abfrage Zusammenführen1 ist die Arbeitsgrundlage für die folgenden Schritte. Aus den 4 Spalten Lieferant#, Lieferant, Obsorte# und Obstsorte  sollen erst einmal 2 Spalten generiert werden. Und Sie erinnern sich, dass eine der Zielsetzungen war, die numerischen Werte in unde Klammer einzufassen. Dazu markieren Sie erst Lieferant#Transformieren | Format | Präfix hinzufügen und geben Sie als Wert für den Päfix die öffnende runde Klammer ( ein. Als Suffix gleich im Anschluß die schließenden Klammer generieren und beide Vorgänge in der Spalte Obstsorte# wiederholen.

Jetzt Lieferant# und Lieferant markieren, Spalten zusammenführen und als Trennzeichen das Leerzeichen wählen. Als Neuer Spaltenname bietet sich Lieferanten an. Gleiche Vorgehensweise bei den Obstsorten, welche auch diese Überschrift bekommen sollen. An dieser Stelle vielleicht noch einmal Schließen & laden in… und sollte es nicht schon vorgegeben sein, dann wählen Sie Nur Verbindung erstellen.

Jetzt haben Sie die Chance, sich eine Liste der Lieferanten-Nummern zu erstellen. Denn für jeden der Lieferanten werden Sie nun eine eigene Abfrage erstellen. Beispielhaft an (1500) Peter Lustig zeige ich Ihnen den Weg Schritt für Schritt auf:

  • Im linken Seitenfenster ein Rechtsklick auf die Query Zusammenführen1.
  • Wählen Sie im Kontextmenü Verweis.
  • Erweitern Sie in dieser neu erstellten Abfrage Zusammenführen1 (2) die Spalte Lieferanten und filtern Sie den ersten Eintrag der Liste (Peter Lustig).
  • Optional sortieren Sie die Spalte Obstsorten, um später immer die gleiche Reihenfolge der Produkte zu haben.
  • Fügen Sie über das Menü eine Indexspalte ein.
  • Wechseln Sie zum Menü Transformieren und achten Sie darauf, dass die Spalte Index markiert ist.
  • In der Gruppe Bedingte Spalte wählen Sie Spalte pivotieren.
  • Im Dialog wählen Sie bei Wertespalten Eintrag Obstsorten, nach einem Klick auf Erweiterte Optionen wählen Sie im dazugehörigen Dropdown Nicht aggregieren.
  • Bestätigen Sie mit OK.
  • Der besseren Transparenz wegen geben Sie dieser Query den Namen der Lieferantennummer, hier also 1500.

Sie erkennen, dass für jedes Produkt des Lieferanten eine eigene Spalte mit einer fortlaufenden, numerischen Überschrift erstellt worden ist. – Diesem Vorgang werden Sie entsprechend für jeden der Lieferanten durchführen. Übrigens werden sie im rechten Seitenfenster auch erkennen, dass die Einzel-Abfragen der Lieferanten automatisch als Nur Verbindung gespeichert worden sind, weil Sie die Quell-Abfrage auch so gesichert hatten; somit wird nicht überflüssiger Weise in jeweils ein neues Tabellenblatt gespeichert werden.

Ihnen stehen in diesem Fall nun zehn einzeilige Abfragen mit den Lieferanten in der ersten Spalte und den entsprechenden lieferbaren Produkten in den folgenden Spalten zur Verfügung. Diese Query sollen nun zu einer einzelnen, kompakten Abfrage zusammengefasst werden. Dazu gehen Sie so vor:

  • Öffnen Sie die erste Abfrage, hier: 1500.
  • Menü Start | Kombinieren | Abfragen anfügen | Abfragen als neu anfügen. Durch diesen kleinen Umweg des neu Einfügens bleiben die Original-Abfragen auch im ursprünglichen Zustand, was mitunter von Vorteil sein kann.
  • Im Anfügen-Dialog markieren Sie als erstes die Auswahl Drei oder mehr Tabellen.
  • Die erste Lieferanten-Tabelle (1500) ist ja im rechten Auswahlfenster des Dialogs bereits eingefügt. Markieren Sie bei Verfügbare Tabellen nun alle weiteren Einträge von 1501 bis (in diesem Fall) 1509 und klicken dann auf die mittige Schaltfläche Hinzufügen.
  • Markieren Sie nun die Spalte 0 bis zur letzten Spalte, hier: 6.
  • Per Rechtsklick in eine der markierten Überschriften Spalten zusammenführen wählen.
  • Als Trennzeichen wählen Sie Komma, der Spaltenname kann erst einmal so bleiben.
  • Um bei Lieferanten mit wenigen Artikeln die angehängten Kommata zu löschen, ersetzen Sie in der Spalte Zusammengeführt zwei direkt aufeinanderfolgende Kommas ,, durch nichts.

Das sieht nun schon fast so aus, wie es im Endeffekt sein soll. Es sind noch zwei Punkte, die einer Korrektur bedürfen. Einerseits soll ja nach jedem trennenden Komma ein Leerzeichen folgen und bei einzelnen Lieferanten ist nach dem letzten Texteintrag der Obstsorten noch ein Komma erhalten geblieben. Den Abstandnach jedem Komma fügen Sie auch über Werte ersetzen… ein, in Sachen „letztes, überflüssiges Komma löschen“ können Sie den Weg beschreiten, dass sie es in Power Query dabei belassen und nach dem Speichern in ein Arbeitsblatt mit Excel-Funktionen den Zustand bereinigen. In dem Fall ändern Sie den Namen der Spalte Zusammengeführt auf Obstsorten.

Sie können das aber auch innerhalb Power Query mit einer etwas umfangreicheren Formel gut erreichen. Wechseln Sie dazu nach Spalte hinzufügen | Benutzerdefinierte Spalte und tragen Sie bei Neuer Spaltenname beispielsweise Obstsorten ein. Im großen Textfeld Benutzerdefinierte Spaltenformel tragen Sie exakt diesen Code nach dem vorgegebenen = ein:

= if Text.EndsWith([Zusammengeführt],", ")
then Text.Start([Zusammengeführt], 
   Text.Length([Zusammengeführt])-1)
else [Zusammengeführt]

Sollten Sie das Leerzeichen nach dem trennenden Komma noch nicht eingefügt haben, muss naturgemäß in der ersten Zeile des Codes das ", " durch ein "," (also ohne das Leerzeichen) ersetzt werden. Anschließend löschen Sie die Spalte Zusammengeführt und speichern Sie über Schließen & laden oder Schließen & laden in… ihr Werk an gewünschter Position in Excel. Damit haben Sie sich nach getaner Arbeit eine Pause und vielleicht auch eine Tasse Kaffee verdient.

▲ nach oben …

Am führenden $-Zeichen dies Beitragsnamens haben Sie vielleicht schon erkannt, dass dieser Beitrag „nur“ der Einstieg zu einem weiteren, tiefergehenden Beitrag zum Thema ist. Und ich verspreche Ihnen, dass dort ein deutlich besserer Weg zum Ziel aufgezeigt wird. Aber…

Ein wichtiger Hinweis
Der Link auf den Folge-Beitrag beginnt mit „$$“. Das bedeutet für alle derartig ausgezeichneten Beiträge, dass diese kostenpflichtig sind (eine geringe Spende).
Näheres dazu in der angesprochenen Mail. Auch Wissen hat einen Wert!

Wie eben schon dargelegt habe ich auch eine ausführliche Beschreibung meines Lösungsweges in bekannter, ausführlicher Form erstellt. Eine E-Mail an mich mit dem Stichwort/Betreff $$ PQ: Gruppieren und verketten mit Power Query und Sie bekommen von mir den entsprechenden Link sowie das erforderliche Passwort zum öffnen des Beitrages, sofern Sie mir eine eine Spende von 7,50 € (Überweisung, Donate-Button, Amazon-Gutschein oder PayPal Freundschaft) haben zukommen lassen.

▲ nach oben …

Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00  freuen … (← Klick mich!)

Dieser Beitrag wurde unter Daten zusammenführen, Filtern & Sortieren, Ohne Makro/VBA, Power Query, Spalten bearbeiten, Text-Behandlung abgelegt und mit , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.