In ei­nem Fo­rum wur­de die an­fangs recht ein­fach schei­nen­de An­fra­ge ge­stellt, wie die in der Über­schrift die­ses Bei­tra­ges beschriebe­nen Auf­gabe zu lö­sen sei. Be­vor eine Mus­ter-xlsx vom Fra­ge­stel­ler ein­ge­stellt wor­den ist, wur­den ver­schiedene Ant­wor­ten auf der Ba­sis VBA und ein Hin­weis auf Pow­er Que­ry ge­ge­ben. Der Fra­ge­stel­ler hat sich let­z­tendlich für eine recht außergewöhn­liche Lö­sung entsch­ieden, wo die Dat­en erst nach Word trans­feriert wur­den und nach ein­er Bear­beitung zu­rück nach Ex­cel im­por­tiert wor­den sind. Na ja, je­dem das sei­ne … 😯 

Ich habe mir vorgenom­men, die­ses Prob­lem mit Pow­er Que­ry zu lö­sen. Der ein­zig wirk­lich exis­tie­ren­de „Stolper­stein” ist das Anführungsze­ichen, welch­es ja mehr­fach als ein­zel­ne, logis­che „Zei­le” inner­halb ein­er Zel­le exis­tiert. Selb­st wenn die ent­spre­chen­de Spal­te in Pow­er Que­ry als Text aus­geze­ich­net wor­den ist, wird PQ die­se Zei­le „unter­schla­gen” und auch anson­sten nicht das tun, was erwartungs­gemäß sein soll­te. Aber se­hen Sie selb­st …

▲ nach oben …

Ein­stieg und Vor­be­rei­tung

Begin­nen Sie da­mit, die­se Mus­ter-xlsx (eine Ko­pie der Da­tei aus dem Fo­rum) nach Pow­er Que­ry zu im­por­tie­ren. Übri­gens: Ich for­matierte den Daten­bere­ich grund­sät­zlich zu­erst in Ex­cel als „Intel­li­gente” Ta­bel­le, be­vor ich die Dat­en nach PQ im­por­tie­re. Das spart mit­un­ter irri­tierende Nach­fra­gen seit­ens des Pro­gramms. Nach dem Im­port stellt sich das im Pow­er Que­ry-Edi­tor so dar:

Die Dat­en di­rekt nach dem Im­port

Ein­steiger in Sa­chen Pow­er Que­ry wer­den sich jet­zt ge­wiss den­ken, dass hier nur die einzel­nen Spal­ten der­art auf­ge­teilt, ges­plit­tet wer­den müs­sen, dass die Zeilen­schal­tun­gen jew­eils eine neue Zei­le erzeu­gen. Das hört sich gut an, führt aber nach dem Tei­len der ers­ten Spal­te zu die­sem Ergeb­nis:

Ein ge­wiss uner­wartetes Ergeb­nis 🙁

Sie erken­nen, dass zwar Spal­te1 nach jed­er Zeilen­schal­tung eine neue Zei­le gener­iert wor­den ist, aber in Spal­te2 nach wie vor meh­re­re (logis­che) Zei­len in ein­er Zel­le sind. Natür­lich wer­den Sie rasch auf die Idee kom­men, gle­ich bei­de Spal­ten zu mar­kie­ren und dann die den Split-Vor­gang durch­zu­füh­ren; aber wenn sie mehr als eine Spal­te mar­kiert ha­ben, ste­ht Ih­nen der Punkt Spal­te tei­len nicht zur Ver­fü­gung. 🙁 Den detail­lierten Weg, die Zeilen­schal­tun­gen als Tren­ner zu ver­wen­den, wer­de ich weit­er un­ten be­schrei­ben. Und natür­lich auch, wie die Auf­tei­lung in neue Zei­len statt Spal­ten mach­bar ist.

Die logis­che Fol­gerung: Jede Spal­te muss ein­zeln in die­ser Wei­se „behan­delt” wer­den. Dar­um lö­schen Sie entwed­er im recht­en Seit­en­fen­ster die bis­lang fehler­haften Schrit­te oder (wenn sie es sich ein­fach ma­chen wol­len) schlie­ßen Sie die­se Ab­fra­ge ohne sie zu spe­ich­ern und begin­nen noch ein­mal ganz von vor­ne.

Sie befind­en sich jet­zt wie­der an dem Punkt, wo die Dat­en frisch in den Edi­tor im­por­tiert wor­den sind. Ide­al­er­weise wer­den sie nun gle­ich an die­ser Stel­le die Ab­fra­ge sich­ern. Dazu ein Klick auf den Text unter­halb des Sym­bols Schlie­ßen & la­den und wäh­len Sie Schlie­ßen & la­den in… aus. Im Dialogfen­ster Klick­en Sie dann auf Nur Ver­bin­dung er­stel­len und bestäti­gen Sie mit OK. Sie kön­nen das di­rekt übri­gens auch über den Me­nü­punkt Da­tei erre­ichen.

Da Pow­er Que­ry durch die­sen Vor­gang ge­schlos­sen wur­de, öff­nen Sie die Ab­fra­ge an­schlie­ßend auf be­lie­bi­ge Wei­se, beispiel­sweise durch ei­nen Dop­pelk­lick im recht­en Seit­en­fen­ster. Erweit­ern Sie das lin­ke Seit­en­fen­ster durch ei­nen Klick auf den senk­recht ste­hen­den Text Abfra­gen. Recht­sklick auf die Zei­le Ta­bel­le1 und wäh­len Sie dann im Kon­textmenü Ver­weis. Wieder­holen Sie die­sen Vor­gang so­fort noch ein­mal. Ob­wohl ich dur­chaus in Sa­chen PQ er­fah­ren bin, än­de­re ich der Klar­heit und Trans­parenz we­gen den Na­men der bei­den neu erstell­ten Abfra­gen auf Ta­bel­le1 (Spal­te1) und Ta­bel­le1 (Spal­te2). Das geht gut per Recht­sklick oder F2.

Da ja ge­wiss im­mer noch die Ab­fra­ge Ta­bel­le1 (Spal­te2) mar­kiert ist, ist die­se auch ak­tiv; ein Klick in die Über­schrift Spal­te1 und die Tas­te Entf oder (gle­ich) ein Recht­sklick in die Über­schrift und im Kon­textmenü Ent­fer­nen. So bleibt nur die Spal­te2 in die­ser Ab­fra­ge erhal­ten (was ja auch dem Na­men der Ab­fra­ge ent­spricht).

Wech­seln Sie zur Ab­fra­ge Ta­bel­le1 (Spal­te1) (per Maus­klick) und dort lö­schen Sie na­tur­ge­mäß Spal­te2. An die­ser Stel­le ein Hin­weis, den ich Ih­nen auch nur ge­ben kann, weil ich über die schon oben ange­sproch­ene Beson­der­heit in Sa­chen Anführungsze­ichen ge­stol­pert bin: Berech­nen Sie ein­fach ein­mal die An­zahl der Zei­len, die nach dem Split­ten ent­standen sein müs­sen. Hier wä­ren das dann für die 4 „echt­en” Zei­len: 3 + 3 + 4 + 5, also 15 Zei­len. Bit­te mer­ken … 😉

Die Über­schrift Spal­te1 ist ja mar­kiert dar­um im Reg­is­ter Home in der Grup­pe Trans­formieren ein Klick auf Spal­te tei­len. Im Kon­textmenü wäh­len Sie natür­lich Nach Trennze­ichen. Im Dialogfen­ster sind schon ver­schiedene Posi­tio­nen aus­ge­füllt. Das Trennze­ichen #(lf) als Syn­onym für die Zeilen­schal­tung ist seit­ens Pow­er Que­ry kor­rekt erkan­nt wor­den:

Vie­les erken­nt Pow­er Que­ry automa­tisch so, wie ge­wollt

Bei Auf­tei­len ist Bei je­dem Vorkom­men des Trennze­ichens mar­kiert, auch das ist rich­tig. Bei Erweit­erte Optio­nen än­dern Sie die Aus­wahl bei Auf­tei­len in auf Zei­len und Klick­en an­schlie­ßend auf OK:

Nicht so gut … 🙁

… Und es sind nur zwölf statt der er­war­te­ten 15 Zei­len. Ins­beson­dere die Zei­len 9 und 12 sind mehr­zei­lig ge­blie­ben und die jew­eils ers­te logis­che Zei­le der Zel­le ist leer. Wenn Sie et­was Ursachen­forschung be­trei­ben wer­den Sie erken­nen, dass die Anführungsze­ichen die Fehlerur­sache sein müs­sen. Ich emp­fin­de das als Feh­ler des Pow­er Que­ry, denn der In­halt der Spal­te ist auch schon im Ur­sprung (Ta­bel­le1) bere­its als Text for­matiert und aus die­sen Grund soll­te auch ein " als nor­males Zei­chen behan­delt wer­den. Also gilt es, die­sen Bug, die­se Klip­pe mög­lichst sich­er zu umschif­f­en.

▲ nach oben …

Feh­ler­kor­rek­tur

Lö­schen Sie erst ein­mal die let­zten Schrit­te. Dazu füh­ren sie im recht­en Seit­en­fen­ster ei­nen Recht­sklick auf die Zei­le Spal­te nach Trennze­ichen tei­len durch und wäh­len Bis zum Ende lö­schen; die Sicher­heitsabfrage bestäti­gen Sie mit Lö­schen. Die Gänse­füßchen sind jet­zt wie­der in den jew­eili­gen Zel­len sicht­bar, also enthal­ten. Wech­seln Sie per Maus­klick zur Ur­sprungs­ab­fra­ge Ta­bel­le1. Mar­kie­ren Sie bei­de Spal­ten und füh­ren Sie in eine der Über­schriften ei­nen Recht­sklick durch. Wer­te erset­zen… und ge­ben Sie bei Zu suchen­der Wert das Anführungsze­ichen " ein, bei Erset­zen durch beispiel­sweise die Til­de ~, so­fern die­ses Zei­chen nicht im Text der Spal­ten vor­kommt. Im Zwei­fel ver­wende ich statt­des­sen bei die­sen Gele­gen­heit­en stets ßß, weil die­se Zeichen­folge in der deut­schen Spra­che (und ge­wiss auch nicht in Fremd­sprachen) vorkom­men kann.

Wech­seln Sie nun wie­der­um zur Ab­fra­ge Ta­bel­le1 (Spal­te1) und jet­zt wer­den Sie ge­wiss erken­nen, war­um ich an­fangs kein Dup­likat der Ta­bel­le1 er­stellt habe, son­dern ei­nen Ver­weis. Die Änderun­gen wur­den automa­tisch über­nom­men. 😎 Wieder­holen Sie nun noch ein­mal den Vor­gang in Sa­chen Spal­te tei­len und sie wer­den erken­nen, dass nun die kor­rek­te An­zahl von 15 Zei­len ge­ge­ben ist.

Wech­seln Sie nun zum Reg­is­ter Spal­te hinzufü­gen und wäh­len dort in der Grup­pe All­ge­mein den Punkt Indexs­palte. Jed­er einzel­nen Zei­le wur­de eine fort­laufende Num­mer, begin­nend mit 0 (null), in der Spal­te In­dex hinzuge­fügt.

Wech­seln Sie zur Ab­fra­ge Ta­bel­le1 (Spal­te2) und über Home | Spal­te tei­len | Nach Trennze­ichen gelan­gen sie wie­der in den entsprechen­den Dia­log. Die­ser stellt sich nun aber et­was an­ders dar, denn als Trennze­ichen wird hier automa­tisch das Zei­chen & vorgeschla­gen. Das ist natür­lich nicht rich­tig, es soll ja wie­der die Zeilen­schal­tung sein.

Lö­schen Sie das Zei­chen & im Text­feld und Klick­en Sie dann auf Erweit­erte Optio­nen. Bei der Gele­gen­heit kön­nen Sie auch gle­ich die Zei­len statt der Spal­ten mar­kie­ren. Entschei­dend aber ist, dass sie bei Mith­il­fe von Son­derze­ichen tei­len ein Häk­chen set­zen. Ein Klick auf Son­derze­ichen ein­fü­gen ▼ erweit­ert die­se Aus­wahl und dort mar­kie­ren Sie Zeilen­vorschub. Automa­tisch wird nun ganz oben bei Trennze­ichen ein­ge­ben oder aus­wäh­len die Zeichen­folge #(lf) in das Text­feld einge­fügt und sie kön­nen nun mit OK bestäti­gen. Sie se­hen, auch hier sind es 15 Zei­len. In die­ser Que­ry wer­den sie eben­falls eine Indexs­palte (wie ge­habt) gener­ieren.

Spe­ich­ern Sie nun noch ein­mal die bei­den gener­ierten Ver­weis-Abfra­gen als Nur Ver­bin­dung. So ver­mei­den Sie, dass zum Ab­schluss nicht erforder­liche, un­nö­ti­ge Ta­bel­len in Ex­cel gespe­ichert wer­den. Übri­gens: Wahrschein­lich reicht es,eine der bei­den Abfra­gen in die­sem Mo­dus zu sich­ern. Meist wird die an­de­re Que­ry gle­icher­maßen als Nur Ver­bin­dung gespe­ichert.

End­spurt

Jet­zt ist die Vorar­beit er­le­digt. Nach dem Schlie­ßen & la­den wer­den Sie die Ab­fra­ge Ta­bel­le1 (Spal­te1) öff­nen. Ein Klick auf Kom­binieren und im Drop­Down erweit­ern Sie den Punkt Abfra­gen zusam­men­führen | Abfra­gen als neue Ab­fra­ge zusam­men­führen. Es öff­net sich die­ses Dia­log-Fen­ster:

Der ini­tiale Zusam­men­führen-Dia­log

Der Sinn die­ses Vorge­hens: Die bei­den einzel­nen, neu erstell­ten Abfra­gen müs­sen wie­der zu ein­er einzi­gen, zusam­men­hän­gen­den Que­ry assem­bliert wer­den. Im obe­ren Fen­ster se­hen Sie ei­nen Aus­zug jed­er Dat­en, die in der ak­tu­el­len Ab­fra­ge enthal­ten sind. Ober­halb die­ses „Kas­tens” ist auch noch der Name der Ab­fra­ge an­ge­ge­ben. Wäh­len Sie nun im mit­ti­gen Text­feld die zwei­te der ver­knüpf­ten Que­ries: Ta­bel­le1 (Spal­te2) und das un­te­re (gro­ße) Fen­ster wird mit den entsprechen­den Dat­en ge­füllt.

Klick­en Sie nun im obe­ren (gro­ßen) Fen­ster als auch un­ten in die Spal­te In­dex. Da­mit wei­sen Sie PQ an, die jew­eils zusam­menge­hören­den Wer­te die­ser Spal­ten so zu ver­knüp­fen, dass die Wer­te der gle­ichen Zei­le (des iden­tis­chen In­dex) kor­rekt zuge­ord­net wer­den. – Sehr weit un­ten im Fen­ster se­hen Sie die Text­zei­le Join-Art und dar­un­ter in ei­nem Text­feld die Aus­wahl Link­er äußer­er Join mit der Erk­lärung, dass alle Zei­len der ers­ten (obe­ren) Ab­fra­ge ver­wen­det und die pas­sen­den Zei­len der an­de­ren Que­ry an­ge­zeigt wer­den:

Der Zusam­men­führen-Dia­log di­rekt vor dem Schlie­ßen

OK und das Ergeb­nis ist für Sie, wenn Sie das Kom­binieren zum ers­ten Mal durch­führen, ge­wiss nicht wirk­lich trans­par­ent:

Nur der In­halt „Ta­ble” (Ta­bel­le) in der neu­en Spal­te …

Erweit­ern Sie die Über­schrift die­ser neu­en, drit­ten Spal­te durch ei­nen Klick auf den Dop­pelpfeil _ und im neu erschiene­nen Dia­log lö­schen Sie das Häk­chen bei In­dex und bei Ur­sprüng­li­chen Spal­tenna­men als Prä­fix ver­wen­den:

Die­se Aus­wahl ist kor­rekt

OK und das Ergeb­nis soll­te (erst ein­mal) überzeu­gen. „Erst ein­mal”, weil ja die Spal­te In­dex im näch­sten Schritt ge­löscht wird und auch noch die Er­satz-Zei­chen für die Gänse­füßchen zu­rück umge­wan­delt wer­den müs­sen. Und das ge­schieht genau­so wie im ers­ten Durch­gang, nur dass jet­zt das Zei­chen ~ (oder beispiel­sweise die Zeichen­folge ßß) durch ein " erset­zt wer­den. Bleibt noch ein Klick auf Schlie­ßen & la­den und die­se Ab­fra­ge wird in ein neu­es Ex­cel Arbeits­blatt ge­schrie­ben. Nun kön­nen Sie die so er­stell­te Ta­bel­le kom­plett mar­kie­ren (geht gut mit ein oder zwei Mal StrgA), auss­chnei­den und an be­lie­bi­ger Posi­tion auf ei­nem gewün­scht­en Arbeits­blatt ein­fü­gen.

▲ nach oben …

Hat Ih­nen der Bei­trag ge­fal­len?
Er­leich­tert die­ser Bei­trag Ihre Ar­beit?

Dann wür­de ich mich über ei­nen Bei­trag Ihrer­seits z.B. 2,50 € freu­en …