$ Gruppieren und verketten mit Power Query

Am Beispiel ein­er Auflis­tung mit Pro­duk­ten und ver­schieden­er Liefer­an­ten dieser Artikel zeige ich Ihnen in diesem Beitrag einen Weg auf, je Liefer­ant die entsprechen­den Pro­duk­te in 1 Zelle, hier jew­eils durch einen Sep­a­ra­tor getren­nt, darzustellen. Laden Sie die Dat­en gerne hier herunter.

Hinweise zu den Roh-Daten

Das Blatt Obst­sorten bedarf prinzip­iell kein­er Erläuterung. Pro­duk­t­num­mer und Pro­duk­t­beze­ich­nung, alles IT-gerecht. Zu Liefer­an­ten ist anzumerken, dass ich ein­fach (mit Aus­nahme der ersten Zeile) aus Grün­den der Neu­tral­ität die Namen aller Bun­deskan­zler der Bun­desre­pub­lik Deutsch­land bis heute (März 2022) ver­wen­det habe. Da sollte jedem klar sein, dass zufäl­lige Namen­sübere­in­stim­mungen mit existieren­den Unternehmen offen­sichtlich nichts mit der Real­ität zu tun haben. 😉

Und das Blatt Roh­dat­en enthält ganz bewusst nur die numerischen Werte für die Liefer­an­ten als auch die Obst­sorten. Ich nutze die Gele­gen­heit, den PQ-Ver­weis sin­nvoll einzuset­zen. Allerd­ings tanzt die Zeile 52 etwas aus der Rei­he. Ein­er­seits gehört sie nicht zur Intel­li­gen­ten Tabelle und ander­er­seits ste­ht in A52 und B52 kein fes­ter Wert son­dern jew­eils eine Formel. Diese kann ich näm­lich gut nutzen, wenn ich ein­mal andere Zufall­szahlen im Bere­ich der Liste darüber gener­ieren und die Formel nicht neu schreiben möchte. (Das habe ich beispiel­sweise genutzt, als ich den nun neuen Bun­deskan­zler Scholz hinzuge­fügt habe.) Und beim Import in Pow­er Query wer­den natür­lich nur die Werte der Liste über­nom­men. 💡

▲ nach oben …

Zielsetzung

Das Ziel dieser Übung ist, eine Tabelle mit der Aus­sage „wer liefert welch­es Obst” zu erstellen. In Spalte A sollen die Namen aller 10 Liefer­an­ten ste­hen und in Spalte B jew­eils in 1 Zelle zusam­menge­fasst all jene Obst­sorten, die der jew­eilige Liefer­ant im Pro­gramm hat. Die Pro­duk­te sollen als Sep­a­ra­tor ein Kom­ma mit nach­fol­gen­dem Leerze­ichen enthal­ten. (Weit­er unten find­en Sie das Wun­schergeb­nis als Screen­shot.)

Ich stelle Ihnen im erweit­erten $$-Beitrag 2 Wege vor, wie sie in Pow­er Query real­isier­bar sind. In Plain Excel gibt es zwar je nach Ver­sion unter­schiedliche Wege, die Texte zu ver­ket­ten, das soll hier aber nicht disku­tiert wer­den. Und Pow­er Query ist hier aus­nahm­sweise ein­mal in Sachen Kom­fort im Hin­tertr­e­f­fen. Sprich: PQ ist für diese Zielset­zung doch aufwendi­ger zu hand­haben, für größere Daten­men­gen jedoch ganz klar im Vorteil.

▲ nach oben …

Vorarbeiten

Ich gehe davon aus, dass Sie dieses File von unserem Serv­er herun­terge­laden haben. Nacheinan­der importieren Sie nun die Tabellen von den Arbeits­blät­tern Obst­sorten, Liefer­an­ten und Roh­dat­en. Da ich den Tabellen bere­its „sprechende” Namen gegeben habe, sind die Beze­ich­nun­gen der einzel­nen Abfra­gen aus­sagekräftig und brauchen nicht geän­dert zu wer­den. Ide­al­er­weise wer­den Sie die einzel­nen Abfra­gen nach dem Import sofort über Schließen & laden in… | Nur Verbindung erstellen sich­ern.

Öff­nen Sie nun die Abfrage Raw­Da­ta und sor­gen Sie im ersten Schritt dafür, dass die bei­den Spal­tenna­men (Über­schriften) jew­eils durch beispiel­sweise ein #-Zeichen ergänzt wer­den. Ich klicke dazu in die Über­schrift, F2, Ende und gebe dann das Zeichen # über die Tas­tatur ein. Der Hin­ter­grund dieser Übung ist, dass nun über den PQ-Ver­weis die alphanu­merischen, les­baren Namen der Liefer­an­ten und Obst­sorten hinzuge­fügt wer­den, den alten Namen (ohne das #-Zeichen) bekom­men sollen und dadurch natür­lich nicht zwei Spal­ten mit iden­tis­chem Namen existieren wer­den (was ja bekan­ntlich auch nicht möglich ist). Und zugegeben, mit genü­gend Erfahrung kön­nen Sie natür­lich auch auf diesen Schritt verzicht­en und PQ die erfordelichen Anpas­sun­gen über­lassen.

Gehen Sie nun über Kom­binieren | Abfra­gen zusam­men­führen | Abfra­gen als neue Abfrage zusam­men­führen, um die Abfrage Raw­Da­ta im ursprünglichen Zus­tand zu belassen und anschließend in diesem Dup­likat im ersten Schritt die der Liefer­an­ten­num­mer zuge­ord­neten Fir­men­na­men zu verknüpfen. Sie wer­den also die Abfrage Liefer­an­ten als zweite Abfrage ein­set­zen. Nach dem schlißen des Dialogs  brauchen Sie beim Erweit­ern nur die Spalte Liefer­an­ten einzufü­gen. Wenn ihnen dieses Vorge­hen per PQ-Ver­weis nicht ganz so geläu­fig ist, kön­nen Sie die detail­lierte Vorge­hensweise hier in unserem Blog nach­le­sen.

Ergänzen Sie nun auf die prinzip­iell gle­iche Weise die Beze­ich­nung der Obst­sorten, nur dass Sie nun die vorhan­dene, ger­ade eben erstellte Abfrage Zusamenfühen1 als Basis (Daten­quelle) ver­wen­den und keine neue Abfrage erstellen. Anschließend posi­tion­ieren, ver­schieben Sie die bei­den neu erstell­ten Spal­ten an die Posi­tion direkt nach dem dazuge­höri­gen numerischen Wert. Der Optik wegen kön­nen Sie die automa­tisch ange­fügte Ergänzung .1 bei den Lieferanten.1 löschen oder gle­ich nur Liefer­ant als Spal­tenüber­schrift ste­hen lassen; und die Über­schrift Beze­ich­nung würde ich in Obst­sorte ändern. Auch diese Abfrage soll­ten Sie über Schließen & laden in… | Nur Verbindung erstellen sich­ern, damit PQ kein neues, eigentlich unnötiges Arbeits­blatt anlegt. 🙂 Ob Sie den Namen dieser Query ändern, über­lasse ich Ihrem Geschmack; ich finde diesen ganz aus­sagekräftig und belasse es dabei.

Damit haben Sie eine solide Basis für die kom­menden Schritte. Die Vorar­beit ist getan. Hier schon ein­mal das Ergeb­nis, welch­es sich am Ende Ihrer Bemühun­gen beispiel­sweise so zeigen soll; es stellt sich in der Excel-Umge­bung so dar:

Beispiel­haftes Teil-Excel-Wun­schergeb­nis, alle liefer­baren Obst­sorten in ein­er Zelle

Die Liefer­an­ten­num­mer und auch die Pro­duk­t­num­mer der Obst­sorten sind zur besseren Separierung in runde Klam­mern einge­fasst. Um ähn­liche Namen für Liefer­an­ten und/oder Pro­duk­te bess­er unter­schei­d­bar zu machen, wurde die mod­i­fizierte Liefer­an­ten-bzw. Pro­duk­t­num­mer mit den alphanu­merischen Dat­en verknüpft. Die liefer­baren Obst­sorten sind für jeden Liefer­an­ten der Auf­gaben­stel­lung entsprechend alle in 1 Zelle jew­eils durch ein Kom­ma mit ange­hängten Leerze­ichen zusam­menge­fasst. Sie kön­nen das natür­lich nach Ihrem eige­nen Geschmack hand­haben. 😎 Aber gle­ich dazu mehr… Ein präven­tiev­er Hin­weis: Fällt Ihnen in Zeile 4 etwas auf? An passender Stelle kann das Ent­fer­nen von Dup­likat­en hil­fre­ich sein, aber das über­lasse ich Ihrem Erfahrungss­chatz. 😉

Und noch ein Hin­weis: Den bis hier­her erar­beit­eten Stand der Dinge brauchen Sie auch als Basis für jene Lösung, welche ich Ihnen am Schluss dieses Beitrages anbi­ete.

▲ nach oben …

Der Lösungsweg

Die hier vorgestellte Lösung der Auf­gabe ist so konzip­iert, dass Sie (mit ein­er einzi­gen Aus­nahme) auss­chließlich über die GUI (die grafis­che Ober­fläche), also mit diversen Mausklicks und ab und zu ein­er Eingabe über die Tas­tatur auskom­men. Und wie auch schon im Vor­bere­itungs-Teil hierüber habe ich ganz bewusst den einen oder anderen Schritt mehr einge­fügt, um Ihnen das Ver­ständ­nis für die einzel­nen Schritte zu erle­ichtern. Und ich ver­sichere Ihnen, dass Sie später, wenn Sie diese Übung das eine oder andere Mal mit Ihren Dat­en durchex­erziert haben, kürzere Wege mit weniger Schrit­ten find­en wer­den. Und alter­na­tiv bleibt Ihnen noch die ganz am Ende dieses Beitrages beschriebene Möglichkeit. 💡

Ich weiß, es sind viele Schritte. Je mehr Liefer­an­ten, umso mehr Zwis­chen­schritte. Darum auch schon an dieser Stelle mein Ratschlag, dass Sie sich gut über­legen, ob sich der erforder­liche Aufwand (dieses Weges) bei mehr als 10, höch­stens 15 Liefer­an­ten lohnt. Aber sehen und entschei­den Sie selb­st! Und wie bere­its ganz zu Beginn geschrieben, in den neueren Vesio­nen des Excel gibt es teil­weise bess­er über­schaubarere Wege. Aber dann spielt Pow­er Query eben keine Rolle… 🙄

Die zulet­zt erstellte Abfrage Zusammenführen1 ist die Arbeits­grund­lage für die fol­gen­den Schritte. Aus den 4 Spal­ten Liefer­ant#, Liefer­ant, Obsorte# und Obst­sorte  sollen erst ein­mal 2 Spal­ten gener­iert wer­den. Und Sie erin­nern sich, dass eine der Zielset­zun­gen war, die numerischen Werte in unde Klam­mer einz­u­fassen. Dazu markieren Sie erst Liefer­ant#,  Trans­formieren | For­mat | Prä­fix hinzufü­gen und geben Sie als Wert für den Päfix die öff­nende runde Klam­mer ( ein. Als Suf­fix gle­ich im Anschluß die schließen­den Klam­mer gener­ieren und bei­de Vorgänge in der Spalte Obst­sorte# wieder­holen.

Jet­zt Liefer­ant# und Liefer­ant markieren, Spal­ten zusam­men­führen und als Trennze­ichen das Leerze­ichen wählen. Als Neuer Spal­tenname bietet sich Liefer­an­ten an. Gle­iche Vorge­hensweise bei den Obst­sorten, welche auch diese Über­schrift bekom­men sollen. An dieser Stelle vielle­icht noch ein­mal Schließen & laden in… und sollte es nicht schon vorgegeben sein, dann wählen Sie Nur Verbindung erstellen.

Jet­zt haben Sie die Chance, sich eine Liste der Liefer­an­ten-Num­mern zu erstellen. Denn für jeden der Liefer­an­ten wer­den Sie nun eine eigene Abfrage erstellen. Beispiel­haft an (1500) Peter Lustig zeige ich Ihnen den Weg Schritt für Schritt auf:

• Im linken Seit­en­fen­ster ein Recht­sklick auf die Query Zusammenführen1.
• Wählen Sie im Kon­textmenü Ver­weis.
• Erweit­ern Sie in dieser neu erstell­ten Abfrage Zusammenführen1 (2) die Spalte Liefer­an­ten und fil­tern Sie den ersten Ein­trag der Liste (Peter Lustig).
• Option­al sortieren Sie die Spalte Obst­sorten, um später immer die gle­iche Rei­hen­folge der Pro­duk­te zu haben.
• Fügen Sie über das Menü eine Indexs­palte ein.
• Wech­seln Sie zum Menü Trans­formieren und acht­en Sie darauf, dass die Spalte Index markiert ist.
• In der Gruppe Bed­ingte Spalte wählen Sie Spalte piv­otieren.
• Im Dia­log wählen Sie bei Wertes­pal­ten Ein­trag Obst­sorten, nach einem Klick auf Erweit­erte Optio­nen wählen Sie im dazuge­höri­gen Drop­down Nicht aggregieren.
• Bestäti­gen Sie mit OK.
• Der besseren Trans­parenz wegen geben Sie dieser Query den Namen der Liefer­an­ten­num­mer, hier also 1500.

Sie erken­nen, dass für jedes Pro­dukt des Liefer­an­ten eine eigene Spalte mit ein­er fort­laufend­en, numerischen Über­schrift erstellt wor­den ist. – Diesem Vor­gang wer­den Sie entsprechend für jeden der Liefer­an­ten durch­führen. Übri­gens wer­den sie im recht­en Seit­en­fen­ster auch erken­nen, dass die Einzel-Abfra­gen der Liefer­an­ten automa­tisch als Nur Verbindung gespe­ichert wor­den sind, weil Sie die Quell-Abfrage auch so gesichert hat­ten; somit wird nicht über­flüs­siger Weise in jew­eils ein neues Tabel­len­blatt gespe­ichert wer­den.

Ihnen ste­hen in diesem Fall nun zehn einzeilige Abfra­gen mit den Liefer­an­ten in der ersten Spalte und den entsprechen­den liefer­baren Pro­duk­ten in den fol­gen­den Spal­ten zur Ver­fü­gung. Diese Query sollen nun zu ein­er einzel­nen, kom­pak­ten Abfrage zusam­menge­fasst wer­den. Dazu gehen Sie so vor:

• Öff­nen Sie die erste Abfrage, hier: 1500.
• Menü Start | Kom­binieren | Abfra­gen anfü­gen | Abfra­gen als neu anfü­gen. Durch diesen kleinen Umweg des neu Ein­fü­gens bleiben die Orig­i­nal-Abfra­gen auch im ursprünglichen Zus­tand, was mitunter von Vorteil sein kann.
• Im Anfü­gen-Dia­log markieren Sie als erstes die Auswahl Drei oder mehr Tabellen.
• Die erste Liefer­an­ten-Tabelle (1500) ist ja im recht­en Auswahlfen­ster des Dialogs bere­its einge­fügt. Markieren Sie bei Ver­füg­bare Tabellen nun alle weit­eren Ein­träge von 1501 bis (in diesem Fall) 1509 und Klick­en dann auf die mit­tige Schalt­fläche Hinzufü­gen.
• Markieren Sie nun die Spalte 0 bis zur let­zten Spalte, hier: 6.
• Per Recht­sklick in eine der markierten Über­schriften Spal­ten zusam­men­führen wählen.
• Als Trennze­ichen wählen Sie Kom­ma, der Spal­tenname kann erst ein­mal so bleiben.
• Um bei Liefer­an­ten mit weni­gen Artikeln die ange­hängten Kom­ma­ta zu löschen, erset­zen Sie in der Spalte Zusam­menge­führt zwei direkt aufeinan­der­fol­gende Kom­mas „ durch nichts.

Das sieht nun schon fast so aus, wie es im End­ef­fekt sein soll. Es sind noch zwei Punk­te, die ein­er Kor­rek­tur bedür­fen. Ein­er­seits soll ja nach jedem tren­nen­den Kom­ma ein Leerze­ichen fol­gen und bei einzel­nen Liefer­an­ten ist nach dem let­zten Tex­tein­trag der Obst­sorten noch ein Kom­ma erhal­ten geblieben. Den Abstand­nach jedem Kom­ma fügen Sie auch über Werte erset­zen… ein, in Sachen „let­ztes, über­flüs­siges Kom­ma löschen” kön­nen Sie den Weg beschre­it­en, dass sie es in Pow­er Query dabei belassen und nach dem Spe­ich­ern in ein Arbeits­blatt mit Excel-Funk­tio­nen den Zus­tand bere­ini­gen. In dem Fall ändern Sie den Namen der Spalte Zusam­menge­führt auf Obst­sorten.

Sie kön­nen das aber auch inner­halb Pow­er Query mit ein­er etwas umfan­gre­icheren Formel gut erre­ichen. Wech­seln Sie dazu nach Spalte hinzufü­gen | Benutzerdefinierte Spalte und tra­gen Sie bei Neuer Spal­tenname beispiel­sweise Obst­sorten ein. Im großen Textfeld Benutzerdefinierte Spal­tenformel tra­gen Sie exakt diesen Code nach dem vorgegebe­nen = ein:

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

Soll­ten Sie das Leerze­ichen nach dem tren­nen­den Kom­ma noch nicht einge­fügt haben, muss naturgemäß in der ersten Zeile des Codes das ", " durch ein "," (also ohne das Leerze­ichen) erset­zt wer­den. Anschließend löschen Sie die Spalte Zusam­menge­führt und spe­ich­ern Sie über Schließen & laden oder Schließen & laden in… ihr Werk an gewün­schter Posi­tion in Excel. Damit haben Sie sich nach getan­er Arbeit eine Pause und vielle­icht auch eine Tasse Kaf­fee ver­di­ent.