PQ: Listeneinträge / Zeilen vervielfachen (Spezial) (3)

Im vorheri­gen Kapi­tel dieser Trilo­gie haben Sie gese­hen, wie es mit mod­erneren Ver­sio­nen des PQ möglich ist, die Tren­nung Prä­fix – Zahl auf recht prob­lem­lose Weise auch dann durchzuführen, wenn der vor­angestellte alphanu­merische Wert aus mehr als 1 Zeichen beste­ht. In den aktuellen Ver­sio­nen ab Excel ²⁰¹⁹⁄₃₆₅ gibt es ja die wirk­lich schicke Möglichkeit, eine Zelle der­art zu split­ten, dass beim Wech­seln von Zeichen zu Zif­fer (und natür­lich auch vice ver­sa ) automa­tisch geteilt wird (siehe Teil 2); aber in den früheren Ver­sio­nen ist es (natür­lich) auch möglich, solch eine Tren­nung mit einiger­maßen über­schaubarem Aufwand durchzuführen. 💡 

Einstieg

Öff­nen Sie das etwas abgeän­derte Muster-File und importieren Sie die Dat­en wie gewohnt in den Pow­er Query-Edi­tor. Anschließend Duplizieren Sie die bei­den ersten Spal­ten auf beliebige Weise. So weit ist es ja so ähn­lich wie bere­its gehabt …

Da jet­zt aber doch so einiges anders läuft und gewiss auch deut­lich aufwändi­ger ist als vielle­icht gedacht schlage ich vor, die Abfrage bere­its an dieser Stelle erst ein­mal über Schließen & laden in… als Nur Verbindung erstellen zu spe­ich­ern.

▲ nach oben …

Mühsam, mühsam … 😥

Die Über­schrift dieses Abschnitts deutet es ja schon an, dass hier einiges mehr an Aufwand erforder­lich ist als in der im zweit­en Teil genan­nten Lösungsmöglichkeit. Aber in den früheren Ver­sio­nen des Pow­er Query gab es noch nicht die Möglichkeit, per Menü­befehl eine Zelle beim Wech­sel von Text zu Zif­fer zu teilen. Aber es sei Ihnen ein Trost: Dieses hier im Anschluss vorgestellte Pro­cedere ist ja nur ein Mal zu Beginn erforder­lich, danach wird ja bei geän­dert­er Daten­lage, beim Klick auf Aktu­al­isieren sehr vieles automa­tisiert ablaufen. Und mein Geheimtipp: Zum Schluss werde ich Sie noch auf einige deut­lich „ele­gan­tere” Möglichkeit­en hin­weisen, welche Sie aber (nur) im $$-Bere­ich dieses Blogs find­en wer­den.

Die Grund-Idee des Ganzen stellt sich so dar: In irgen­dein­er Form muss ich fest­stellen, wie viele Zif­fern am Ende der Zeichen­kette ste­hen. Dann ist es gut möglich, über eine Funk­tion welche der RECHTS() – Funk­tion in Excel gle­icht, genau diese Zif­fer­n­folge zu extrahieren, abzuschnei­den. Diese Zif­fern wer­den dann in eine „echte” Zahlen umge­wan­delt, weil PQ für die Lis­ten-Erweiterung nur den Daten­typ  Zahl ver­wen­den kann.

Soweit die The­o­rie. Und in der Prax­is lässt sich das auch dur­chaus umset­zen. Selb­st wenn Sie im Prinzip nur die alphanu­merischen Zeichen löschen wollen wäre der Aufwand unver­hält­nis­mäßig groß, jedes denkbare Zeichen durch "" (also nichts, einen Leer­String) zu erset­zen. Auf der anderen Seite gibt es aber nur 10 unter­schiedliche Zif­fern. Und genau die wer­den sie nacheinan­der durch "" erset­zen.

Markieren Sie also die bei­den kopierten Spal­ten und Trans­formieren. Ein Klick auf das Sym­bol Werte erset­zen, geben Sie bei Zu suchen­der Wert die Zif­fer 0 ein und belassen das Feld Erset­zen durch leer. Bestäti­gen Sie mit OK und in den bei­den markierten Spal­ten sollte jede 0 durch nichts erset­zt, also gelöscht wor­den sein. Hin­weis: Das Textfeld leer lassen, nicht "" eingeben! Kurze Kon­trolle und es stellt sich her­aus, dass da irgen­det­was anders gelaufen ist als gewollt. In der Spalte Wert bis – Kopie ist in den ersten bei­den und der vor­let­zten Zeile jew­eils eine 0 enthal­ten, aber die Nullen sind nach wie vor sicht­bar. 😥 

Da stellt sich doch wieder ein­mal die Frage, warum der Men­sch denkt, Pow­er Query aber lenkt. 😉 Im Hin­blick darauf, dass PQ doch sehr strin­gent auf den Daten­typ achtet spielt genau das hier eine, nein die entschei­den­den Rolle; es liegt kein ein­heitlich­er Daten­typ vor und da  Zahlen (hier die 10) als Ganzes und nicht als Zif­fern 1 und 0 erkan­nt wer­den, kann PQ nicht wie gewün­scht agieren. In der Zeile 8 ist die Zif­fer 0 zwar ganz ein­deutig ein Teil eines Textes aber der Daten­typ der gesamten Spalte ist Beliebig und auch hier wird aus diesem Grunde keine Erset­zung vorgenom­men. Fol­gerung: Markieren Sie die bei­den … Kopie ‑Spal­ten und weisen Ihnen den Daten­typ  Text zu, um eine klare Lin­ie zu erschaf­fen.

Löschen Sie im recht­en Seit­en­fen­ster den zuvor erstell­ten Schritt (Erset­zter Wert), noch ein­mal Werte erset­zen aufrufen und fügen Sie erneut die 0 an entsprechen­der Posi­tion ein. Im Gegen­satz zum „Durch­gang” eben nicht sofort OK son­dern ein auf Klick auf den neu erschiene­nen Text Erweit­erte Optio­nen und acht­en Sie darauf, dass bei Gesamten Zellinhalt ver­gle­ichen kein Häkchen geset­zt ist. Nun ist der Erfolg gegeben.

▲ nach oben …

Sie wer­den es ahnen: Diesen Vor­gang wer­den sie auch für die Zif­fern l bis 9 durch­führen, gerne auch per Recht­sklick und dann im Kon­textmenü Werte erset­zen… auswählen; die einzige Aus­nahme: Sie brauchen nicht mehr darauf zu acht­en, dass das Häkchen bei Gesamten Zel­len­in­halt ver­gle­ichen geset­zt ist oder nicht, denn dieser Zus­tand wird sich nicht von alleine ändern. Nun ste­ht in jed­er Zeile dieser bei­den Spal­ten nur noch der reine alphanu­merischen Text (das Prä­fix) oder eben gar nichts, wenn im Quell­bere­ich auss­chließlich die Zahl ver­merkt war.

Im näch­sten Schritt wer­den Sie berech­nen, wie viele Zif­fern vom ursprünglichen Text abgeschnit­ten wor­den sind. Ein­fach aus­ge­drückt: Sie sub­trahieren die Länge des gekürzten Textes von der Länge des Orig­inal­textes und das Ergeb­nis entspricht dem gewün­scht­en Wert der Anzahl von Zif­fern. Dazu erstellen Sie über das Menü Spalte hinzufü­gen eine neue, Benutzerdefinierte Spalte und tra­gen dort als Über­schrift beispiel­sweise ## von und bei Benutzerdefinierte Spal­tenformel diese Formel ein:

= Text.Length(Text.From([Wert von]))
   -Text.Length([#"Wert von - Kopie"])

Dass Sie mit Text.Length() die Länge eines Textes berech­nen, ist prinzip­iell selb­sterk­lärend (sofern Sie der englis­chen Sprache einiger­maßen mächtig sind). Die Funk­tion Text.From() ist erforder­lich, da in der Spalte Wert von ja teil­weise Zahlen ste­hen; diese wer­den auf diese Weise zum Daten­typ  Text umge­wan­delt. – Das gle­iche Vorge­hen wer­den Sie nun mit der Spalte Wert bis durchex­erzieren, natür­lich mit der Über­schrift ## bis. Auch hier wird die jew­eilige Länge der Zif­fern berech­net und als numerisch­er Wert zuück­gegeben.

▲ nach oben …

Die Anzahl der Zif­fern in den Spal­ten Wert von und Wert bis sind ja in den eben berech­neten Spal­ten ## von und ## bis berech­net wor­den. Auf dieser Basis extrahieren Sie die Zahlen von den ver­bun­de­nen alphanu­merischen Werten der ersten bei­den Spal­ten. Dazu set­zen Sie die M-Funk­tion Text.End() in je ein­er neu hinzuge­fügten Benutzerdefinierten Spalte mit den Spal­tenna­men von und bis fol­gen­der­maßen ein:

Text.End(Text.From([Wert von].), #[## von]) 

bzw.

Text.End(Text.From([Wert bis]), #[## bis])

An der links­bündi­gen Aus­rich­tung der Ergeb­nisse  erken­nen Sie, dass es sich um einen Text han­delt. Darum soll­ten Sie jew­eils eine Umwand­lung dieser bei­den Ergeb­nisse in den Daten­typ  Ganze Zahl vornehmen. – Wenn Sie mögen, kön­nen Sie jet­zt ja schon einige Spal­ten löschen, die nicht mehr benötigt wer­den: Werte bis – Kopie (die ist iden­tisch zu Werte von – Kopie), ## von und ##  bis. Das schafft etwas mehr Über­sicht. 😉 

Die restlichen Schritte erfol­gen nach dem gle­ichen Muster wie in Teil 1 bere­its besprochen, darum hier nur die stich­wor­tar­tige Aus­führung:

• Spalte hinzufü­gen, Benutzerdefinierte Spalte
• Neuer Spal­tenname beispiel­sweise: von-bis
• Benutzerdefinierte Spal­tenformel: {[von].. [bis]}
• Die Spalte von-bis erweit­ern
• Die Spal­ten von-bis, Strg Vari­able 1 und Strg Vari­able 2 in dieser Rei­hen­folge markieren.
• Recht­sklick in eine der markierten Über­schriften und Spal­ten zusam­men­führen wählen
• Trennze­ichen: –Keine–, Neuer Spal­tenname Wert
• Spalte Wert markieren, Recht­sklick und Andere Spal­ten ent­fer­nen.

Schließen & laden oder Schließen & laden in… und das gewün­schte Ziel ist erre­icht. Falls Sie mein­er Empfehlung gefol­gt sind und die Abfrage bere­its zu Beginn per Schließen & laden in… als Nur Verbindung gespe­ichert hat­ten, dann hil­ft Ihnen dieser Tipp, die Dat­en in das Tabel­len­blatt zu bekom­men.

▲ nach oben …

Epilog

Es gibt auch in Pow­er Query noch weit­ere Lösungswege, auch für die Ver­sio­nen vor Excel 2016. Auch kön­nen Sie die hierüber beschriebe­nen Vorge­hensweis­es etwas „schlanker” gestal­ten. Da aber in den meis­ten Fällen ein der­ar­tiges kom­prim­ieren dazu führt, dass der erzeugte M-Code an Trans­parenz lei­det, verzichte ich auf der­atige Schritte.