$ PQ: Spalten bedingt zusammenfügen

Zugegeben, dieses ist nicht die opti­male Meth­ode, um per Pow­er Query Spal­ten, welche einem gewün­scht­en Kri­teri­um entsprechen, zusam­men­zuführen. Aber es ist ein Weg, um mit möglichst wenig „abstrak­ten” Formeln und Funk­tio­nen sowie ohne Pro­gram­mierung zum Ziel zu gelan­gen. Sie nutzen  in dieser Übung Pow­er Query und kom­men auss­chließlich mit eini­gen (mehr) Mausklicks zum Ziel. Und vor allen Din­gen: Der hier gezeigte Weg ist für Sie kosten­frei (ohne eine Spende) nachzule­sen und abzuar­beit­en. Für eine kleine Spende (5 Euro) zeige ich Ihnen aber auch in einem getren­nten $$-Beitrag einen deut­lich effek­tiv­eren Weg auf. Mehr dazu am Ende dieser Abhand­lung.

Ich habe für Sie eine etwas umfan­gre­ichere Daten­ba­sis erstellt, als *.csv gespe­ichert und Ihnen hier auf unserem Serv­er zum Down­load bere­it­gestellt; Hin­weis: Unbe­d­ingt nur herun­ter­laden und spe­ich­ern, nicht (direkt) mit Excel öff­nen, denn diese *.csv wer­den Sie gle­ich in Pow­er Query importieren. Es sind die gle­ichen 1.000 Namen wie in dem in der einen oder anderen Auf­gabe ver­wen­de­ten File mit den Geburt­stags­dat­en; hier sind aber die Geburt­stage ent­fer­nt und 4 weit­ere Spal­ten für die Ver­wend­barkeit in Abteilun­gen (Skills) hinzuge­fügt. Hat ein Mitar­beit­er die entsprechende Fähigkeit ist in der Spalte ein x einge­tra­gen, son­st ist das Feld leer. Und bitte nicht in Sachen „Kom­pat­i­bil­ität” der Auf­gaben wun­dern, ich habe das Zufall­sprinzip per Excel „entschei­den” lassen … 😉 Das endgültige Ziel ist, nach den ersten 2 Spal­ten mit Vor­name und Name nur noch 1 weit­ere Spalte (Skills) zu haben, wo alle Ein­satzmöglichkeit­en aufge­führt sind, jew­eils durch einen Kom­ma mit nach­fol­gen­dem Leerze­ichen getren­nt. Die ersten Zeilen der Lösungsta­belle wer­den sich schlußendlich so darstellen:

So soll das Endergeb­nis ausse­hen

Um die Auf­gabe gemein­sam mit mir zu erar­beit­en öff­nen Sie Excel erst ein­mal mit einem leeren Work­book. Wech­seln Sie zum Reg­is­ter Dat­en und Klick­en Sie im Menüband auf das Sym­bol Aus Text/csv. Wählen Sie im Dia­log die eben herun­terge­ladene Datei und das anfängliche PQ-Dialogfen­ster stellt sich so dar:

Die Ansicht als Import-Assis­tent

Grund­sät­zlich ist es nun egal, ob sie eine Option aus der Schalt­fläche Laden wählen oder auf Dat­en trans­formieren Klick­en. Ich ziehe dieses Mal den Weg über Laden vor und wäh­le die Option Laden in… Im fol­gen­den Dia­log wäh­le ich dann die Möglichkeit, die Dat­en im beste­hen­den Arbeits­blatt in der Zelle $A$1 zu spe­ich­ern:

Hier soll das Ergeb­nis der Abfrage gespe­ichert wer­den

Die erste Zeile der csv-Datei mit den Über­schriften sowie die restlichen Zeilen mit den 1.000 Namen wer­den nun in das erste Arbeits­blatt an die gewün­schte Posi­tion geschrieben. Außer­dem wird im recht­en Seit­en­fen­ster automa­tisch eine Abfrage mit dem Namen 1000_Namen_mit_Skills angelegt. Sie kön­nen nun in Excel die automa­tisch gener­ierten Über­schriften der Tabelle (Column1 bis Column7) durch die Inhalte der eigentlichen Über­schriften in der jet­zi­gen Zeile 2 erset­zen. Mit ein­er nicht wirk­lich trans­par­enten Formel kön­nten Sie dann (eben­falls in Plain Excel) die entsprechen­den Felder nach einiger Vorar­beit zusam­men­führen. Aber hier geht es ja um  den Ein­satz von Pow­er Query und darum belassen Sie es bei dieser ursprünglichen Darstel­lung und wer­den die Dat­en beispiel­sweise durch einen Dop­pelk­lick auf die (einzige) Abfrage im recht­en Seit­en­fen­ster den PQ-Edi­tor öff­nen.

Als erstes es ist natür­lich wichtig, die automa­tisch vergebe­nen Über­schriften durch die Inhalte der ersten Daten­zeile (also den eigentlichen Über­schriften) zu erset­zen. Dazu wählen Sie im Menüband Erste Zeile als Über­schriften ver­wen­den und dieses Ziel ist sofort erre­icht.

Das erste Ergeb­nis mein­er fol­gen­den Über­legun­gen war, die einzel­nen Spal­ten ein­fach per M-Funk­tion zu ver­ket­ten. Okay, dazu habe ich etwas pro­gram­miert. Hin­weis: Über­sprin­gen Sie gerne dieses kleine Exper­i­ment, es führt nicht zum gewün­scht­en Erfolg. Aber es ist vielle­icht auch lehrre­ich für Sie! Falls Sie es am eige­nen Rech­n­er nachvol­lziehen wollen: Wech­seln Sie zu Spalte hinzufü­gen | Benutzerdefinierte Spalte und geben Sie bei Neuer Spal­tenname den Text Skills ein. Ich habe nun diese Formel entwick­elt:

Der Ver­such war es wert…

… Und obwohl ein­deutig keine Syn­taxfehler erkan­nt wor­den sind, ist das Ergeb­nis nicht nur ernüchternd, es ist nieder­schmetternd. Error, Error, Error … Auch län­geres herum­ex­per­i­men­tieren auf diesem Niveau hat nicht zum gewün­scht­en Erfolg geführt. Also gilt es, einen anderen Weg zu find­en und zu beschre­it­en. Falls Sie diesem Exper­i­ment gefol­gt sind: Löschen Sie im recht­en Seit­en­fen­ster den let­zten Angewen­de­ten Schritt und set­zen danach an einem prinzip­iell ganz anderen Punkt an.

Das (neue) Ziel des ersten Schrittes ist, jedes x durch den Text der Über­schrift der entsprechen­den Spalte zu erset­zen. Und an dieser Stelle merken Sie schon, dass solch ein Vorge­hen bei diesen fünf Spal­ten noch hin­nehm­bar ist, bei deut­lich mehr Skills ist der Aufwand aus mein­er Sicht nicht mehr wirk­lich trag­bar. Markieren Sie also die erste Skill-Spalte Lager und erset­zen Sie auf beliebi­gen Wege das x mit Lager. Gle­ich­es Vorge­hen bei den Spal­ten Ver­sand, Aus­liefer­ung, Verkauf und Team­leitung.

Nun markieren Sie zuerst die Spalte Lager, Shift und dann ein Klick auf die Über­schrift Team­leitung. Dadurch sind alle Skill-Spal­ten markiert. Wech­seln Sie zum Menü Trans­formieren und ein Klick auf Spal­ten zusam­men­führen. Wählen Sie dann bei Trennze­ichen das Kom­ma und bei Neuer Spal­tenname (option­al) tra­gen Sie beispiel­sweise Skills ein. Nach einem Klick auf OK sieht das ja schon deut­lich bess­er aus als beim ersten Ver­such. 😎

Sie wer­den rasch erken­nen, dass auch leere Felder mit über­nom­men wor­den sind, was sich durch mehrere direkt aufeinan­der­fol­gende, Kommas/Kommata darstellt. Und in vie­len Fällen begin­nt oder endet der Zellinhalt mit min­destens einem Kom­ma. Und wenn ein Mitar­beit­er derzeit keines der Skills erfüllt, dann sehen Sie wie bei Alain Kuhn (Zeile 25) nur die tren­nen­den Kom­mas.

Um auss­chließlich per Mausklick zum Ziel zu gelan­gen, gehe ich fol­gen­den Weg:

• Jedes Kom­ma in der Spalte Skills durch ein Leerze­ichen erset­zen.
• Trans­formieren | For­mat | Kürzen, um die führen­den und ange­hängten Leerze­ichen zu ent­fer­nen.
• Werte erset­zen | (3 Leerze­ichen) durch 1 Leerze­ichen erset­zen lassen.
• Werte erset­zen | (2 Leerze­ichen) durch 1 Leerze­ichen erset­zen lassen; dadurch ist gewährleis­tet, dass immer nur ein tren­nen­des Leerze­ichen gegeben ist.
• Werte erset­zen | (1 Leerze­ichen) durch Kom­ma mit 1 ange­fügten Leerze­ichen erset­zen lassen.
• Bei Bedarf kön­nen Sie nun noch die Felder ohne Inhalt (hier nicht dargestellt durch null son­dern durch keinen Text/leer) durch den Wert (kein Skill) erset­zen lassen; dazu lassen Sie beim erset­zen das obere Feld frei (leer) und tra­gen in das untere Feld den kün­ftig sicht­baren Wert ein.
• Schließen & laden oder Schließen & laden in… und das Ziel ist erre­icht.

Last but not least ein Hin­weis: Die Skills für jeden Namen habe ich per Zufalls­funk­tion verteilt; wenn also ein­mal die Team­leitung auch für alle anderen Skills einen Ein­trag hat, ist er zwar nicht logisch aber denkbar (und mein­er Arbeit­serspar­nis beim erstellen geschuldet).

Wie eben schon dargelegt habe ich auch einen weit­eren Lösungsweg in bekan­nter, aus­führlich­er Form erstellt. Eine E‑Mail an mich mit dem Stichwort/Betreff $$ PQ: Spal­ten bed­ingt zusam­men­führen und Sie bekom­men von mir den entsprechen­den Link sowie das erforder­liche Pass­wort zum öff­nen des Beitrages, sofern Sie mir eine eine Spende von 5,00€ (Über­weisung, Donate-But­ton oder Pay­Pal Fre­und­schaft) haben zukom­men lassen. Und ver­sprochen, das geht auch pri­ma mit sehr vie­len Skills (und auch mit rel­a­tiv wenig Aufwand)!