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

In diesen derzeit aktuellen Ver­sio­nen gibt es eine 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. In diesem Beitrag gehe ich detail­liert nur auf diese spezielle Funk­tion­al­ität ein. All jene Schritte, die im ersten Teil schon aus­führlich disku­tiert wor­den sind, werde ich hier nur stich­wor­tar­tig erwäh­nen.

Einstieg

Sie wer­den das Muster-File öff­nen und die Dat­en der Tabelle im Bere­ich A4:D11 wie gewohnt in den Pow­er Query-Edi­tor importieren. Anschließend Duplizieren Sie die erste Spalte auf beliebige Weise. Ignori­eren Sie die Tat­sache, dass derzeit ‑wenn über­haupt- der Prä­fix nur 1 Zeichen lang ist. Das wer­den Sie näm­lich etwas später ändern.

Markieren Sie die eben erstellte Dup­likat-Spalte Wert von – Kopie und wech­seln Sie zum Menü Trans­formieren. Klick­en Sie auf das Sym­bol Textspalte, anschließend auf Spalte teilen und wählen Sie Nach Wech­sel von Nicht-Zif­fer zu Zif­fer aus:

Die unter­ste Zeile der Auswahl ist hier die richtige…

▲ nach oben …

Werte trennen

Zugegeben, ich war erst reich­lich mis­strauisch, ob das Vorhaben klap­pen würde. Prinzip­iell ist Pow­er Query ja ziem­lich peni­bel in Sachen Daten­typen, und die ersten 4 Zeilen sind ja (eigentlich) vom Typ Ganze Zahl, also kein Text. Aber PQ erledigt den Job ohne Mur­ren. Das Ergeb­nis mag etwas anders sein als erwartet, aber aus logis­ch­er Sicht passt das:

Vielle­icht uner­wartet, aber kor­rekt…

Mein Ziel ist es, möglichst wenig M-Funk­tio­nen im Funk­tions-Edi­tor zu ver­wen­den. Lieber ein oder zwei Schritte mehr, dafür aber trans­par­enter. 😎

• Sie wech­seln zum Menü Spalte hinzufü­gen und dort Bed­ingte Spalte auswählen.
• Als Neuer Spal­tenname geben Sie Prä­fix ein …
• … und in der Zeile Wenn wählen Sie bei Spal­tenname im Drop­Down die Zeile Wert von – Kopie.2
• Als Oper­a­tor nehmen Sie entspricht nicht,
• in das Textfeld Wert schreiben Sie null
• … und in Aus­gabe kommt ‑nach­dem Sie das kleine Fen­ster links des Textfeldes erweit­ert und dort Spalte auswählen angek­lickt haben- Wert von – Kopie.1 .
• Last but not least tra­gen Sie bei Son­st den Text null ein.
• Mit OK schließen Sie den Dia­log.

In der Spalte Prä­fix ste­ht nun null (also leer) oder der existierende Prä­fix, hier stets mit der stets gle­ichen Länge von einem Zeichen.

Denkpause… 💡

▲ nach oben …

Jet­zt geht es daran, die numerischen Werte der Spalte Wert von zu extrahieren. Sie wis­sen und sehen auch, dass die separi­erten Werte bere­its in eini­gen Zeilen der Spalte Wert von – Kopie.1 existieren. Um die zu extrahieren, vol­lziehen Sie im Prinzip die weit­eren Schritte auf die gle­iche Weise wie eben beim Prä­fix. Ich zeige Ihnen hier ein­fach ein­mal das Bild­schirm­fo­to für das Split­ten via Bed­ingte Spalte:

Es geht also, ohne eine einzige Zeile Code einzugeben …

Ich muss geste­hen, dass ich in den meis­ten Fällen den Code, die M-Funk­tio­nen in den Funk­tions-Edi­tor als Benutzerdefinierte Spalte eingebe (siehe auch weit­er unten für den bis-Wert). Das liegt aber gewiss daran , dass ich vor der PQ-Zeit viel in VBA pro­gram­mierte; da geht mir die Text-Darstel­lung leichter von der Hand und ist für mich auch über­sichtlich­er, trans­par­enter. 😉 Aber wenn ich zwecks Kor­rek­tur auf diesen Ein­trag im recht­en Seit­en­fen­ster einen Dop­pelk­lick mache, dann „lande” ich unweiger­lich auch im hierüber gezeigten Dialogfen­ster. Na ja… 🙄

Das gle­iche Vorge­hen ist nun ‑natür­lich sit­u­a­tion­sangepasst- für die Spalte Wert bis ange­sagt. Erst Spalte duplizieren, dann den Prä­fix von der Zahl abtrennen/teilen und die Spalte bis gener­ieren. Eine zweite Prä­fix-Spalte brauchen Sie nicht, denn es würde jeglich­er Logik wider­sprechen, wenn dort ein unter­schiedlich­er Prä­fix ste­hen würde. – Außer Konkur­renz hier für alle, die (wie ich) auch liebe mit Code arbeit­en oder es ein­mal aus­pro­bieren möcht­en:

if [#"Wert bis - Kopie.2"]<>null then
    [#"Wert bis - Kopie.2"]
 else
    [#"Wert bis - Kopie.1"]

▲ nach oben …

Im näch­sten Schritt wer­den Sie alle nicht mehr benötigten Spal­ten löschen. Markieren Sie nacheinan­der die ersten 4 Spal­ten (Wert von, Shift (also bis), Vari­able 2) und mith­il­fe Strg dann noch Prä­fix, von und bis. Andere Spal­ten ent­fer­nen und das sieht schon wesentlich über­sichtlich­er aus. Sie haben nun fast die gle­iche Aus­gangslage, wie sie im ersten Teil dieser Trilo­gie existiert. Nur die Spal­ten von und bis sind hier vom Daten­typ Text. Jet­zt fol­gen diese Schritte:

• Die bei­den Spal­ten von und bis als Typ: Ganze Zahl umwan­deln.
• Spalte hinzufü­gen und Benutzerdefinierte Spalte anklick­en.
• Neuer Spal­tenname: Werte und diese Formel eingeben: {[von]..[bis]}
• Die neue Spalte Werte erweit­ern und Auf neue Zeilen ausweit­en.
• Kon­trol­lieren Sie, ob erst ein­mal alles okay ist:

Das vor­läu­fige Ergeb­nis ist so, wie es sein soll

• Markieren Sie Prä­fix, Strg und Werte und per Recht­sklick in eine Über­schrift Spal­ten zusam­men­führen. Kein Trennze­ichen. Gerne Wert (Sin­gu­lar) als Über­schrift wählen.
• Die Spal­ten Wert, Vari­able 1 und Vari­able 2 in dieser Rei­hen­folge markieren.
• Recht­sklick in eine der markierten Über­schriften und Andere Spal­ten ent­fer­nen.
• Schließen & laden oder Schließen & laden in… und das Werk ist getan. 🙂

Erst ein­mal zumin­d­est… Denn bis zu diesem Zeit­punkt ist die Vorge­hensweise zwar eine ganz andere als im ersten Teil gezeigt, aber das Ergeb­nis ist „natür­lich” gle­ich, denn die Aus­gangslage ist ja auch die selbe. Sie kön­nten also nach Belieben frei entschei­den, welchen Weg Sie gehen wollen. Zumin­d­est, sofern Ihre Excel-Ver­sion min­destens 2019 oder 365 ist..

Ganz zu Beginn hat­te ich Ihnen ja schon „ange­dro­ht”, dass der Prä­fix nicht immer nur aus 1 alphanu­merischen Zeichen beste­hen muss, es kön­nen auch ein­mal mehr sein. Die Gele­gen­heit ist gut, Sie befind­en sich ja ger­ade in der Arbeitsmappe und kön­nen somit auch die Quell­dat­en verän­dern.

Erweit­ern Sie die Tabelle Ist um 1 Zeile und tra­gen Sie einen (beispiel­sweise) 4‑stelligen Prä­fix ein, ich bin bequem und nehme ASDF123 als Wert für von. In B12 trage ich ASDF125 ein und irgendwelche Werte in die bei­den andern Spal­ten.

Ein Recht­sklick in die durch Pow­er Query erstellte Tabelle und Aktu­al­isieren. Herun­ter­scrollen zum Ende der Tabelle und ich bin zufrieden. 😎 Sie hof­fentlich auch, son­st „dür­fen” Sie gerne auf die Fehler­suche gehen. 💡

▲ nach oben …