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

Xtract: Vervielfältigen von Zeilen einer Liste, fortlaufende Nummerierung nach in Spalten hinterlegten Werten (von..bis). Gleiche Daten wie in Teil 1, der Weg ist anders und bedingt eine Excel-Version ab 2019 bzw. 365.

Wissensstand: Level 3 ⇒ Excel GUT!, mindestens Basis-Kenntnisse in Power Query

Versionen ab 2019/365

In diesen derzeit aktuellen Versionen gibt es eine wirklich schicke Möglichkeit, eine Zelle derart zu splitten, dass beim Wechseln von Zeichen zu Ziffer (und natürlich auch vice versa ) automatisch geteilt wird. In diesem Beitrag gehe ich detailliert nur auf diese spezielle Funktionalität ein. All jene Schritte, die im ersten Teil schon ausführlich diskutiert worden sind, werde ich hier nur stichwortartig erwähnen.

Einstieg

Sie werden das Muster-File öffnen und die Daten der Tabelle im Bereich A4:D11 wie gewohnt in den Power Query-Editor importieren. Anschließend Duplizieren Sie die erste Spalte auf beliebige Weise. Ignorieren Sie die Tatsache, dass derzeit -wenn überhaupt- der Präfix nur 1 Zeichen lang ist. Das werden Sie nämlich etwas später ändern.

Markieren Sie die eben erstellte Duplikat-Spalte Wert von – Kopie und wechseln Sie zum Menü Transformieren. Klicken Sie auf das Symbol Textspalte, anschließend auf Spalte teilen und wählen Sie Nach Wechsel von Nicht-Ziffer zu Ziffer aus:

Die unterste Zeile der Auswahl ist hier die richtige…

▲ nach oben …

Werte trennen

Zugegeben, ich war erst reichlich misstrauisch, ob das Vorhaben klappen würde. Prinzipiell ist Power Query ja ziemlich penibel in Sachen Datentypen, und die ersten 4 Zeilen sind ja (eigentlich) vom Typ Ganze Zahl, also kein Text. Aber PQ erledigt den Job ohne Murren. Das Ergebnis mag etwas anders sein als erwartet, aber aus logischer Sicht passt das:

Vielleicht unerwartet, aber korrekt…

Mein Ziel ist es, möglichst wenig M-Funktionen im Funktions-Editor zu verwenden. Lieber ein oder zwei Schritte mehr, dafür aber transparenter. 😎

  • Sie wechseln zum Menü Spalte hinzufügen und dort Bedingte Spalte auswählen.
  • Als Neuer Spaltenname geben Sie Präfix ein …
  • … und in der Zeile Wenn wählen Sie bei Spaltenname im DropDown die Zeile Wert von – Kopie.2
  • Als Operator nehmen Sie entspricht nicht,
  • in das Textfeld Wert schreiben Sie null
  • … und in Ausgabe kommt -nachdem Sie das kleine Fenster links des Textfeldes erweitert und dort Spalte auswählen angeklickt haben- Wert von – Kopie.1 .
  • Last but not least tragen Sie bei Sonst den Text null ein.
  • Mit OK schließen Sie den Dialog.

In der Spalte Präfix steht nun null (also leer) oder der existierende Präfix, hier stets mit der stets gleichen Länge von einem Zeichen.

Denkpause… 💡

▲ nach oben …

Jetzt geht es daran, die numerischen Werte der Spalte Wert von zu extrahieren. Sie wissen und sehen auch, dass die separierten Werte bereits in einigen Zeilen der Spalte Wert von – Kopie.1 existieren. Um die zu extrahieren, vollziehen Sie im Prinzip die weiteren Schritte auf die gleiche Weise wie eben beim Präfix. Ich zeige Ihnen hier einfach einmal das Bildschirmfoto für das Splitten via Bedingte Spalte:

Es geht also, ohne eine einzige Zeile Code einzugeben …

Ich muss gestehen, dass ich in den meisten Fällen den Code, die M-Funktionen in den Funktions-Editor als Benutzerdefinierte Spalte eingebe (siehe auch weiter unten für den bis-Wert). Das liegt aber gewiss daran , dass ich vor der PQ-Zeit viel in VBA programmierte; da geht mir die Text-Darstellung leichter von der Hand und ist für mich auch übersichtlicher, transparenter. 😉 Aber wenn ich zwecks Korrektur auf diesen Eintrag im rechten Seitenfenster einen Doppelklick mache, dann “lande” ich unweigerlich auch im hierüber gezeigten Dialogfenster. Na ja… 🙄

Das gleiche Vorgehen ist nun -natürlich situationsangepasst- für die Spalte Wert bis angesagt. Erst Spalte duplizieren, dann den Präfix von der Zahl abtrennen/teilen und die Spalte bis generieren. Eine zweite Präfix-Spalte brauchen Sie nicht, denn es würde jeglicher Logik widersprechen, wenn dort ein unterschiedlicher Präfix stehen würde. – Außer Konkurrenz hier für alle, die (wie ich) auch liebe mit Code arbeiten oder es einmal ausprobieren möchten:

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

▲ nach oben …

Im nächsten Schritt werden Sie alle nicht mehr benötigten Spalten löschen. Markieren Sie nacheinander die ersten 4 Spalten (Wert von, Shift (also bis), Variable 2) und mithilfe Strg dann noch Präfix, von und bis. Andere Spalten entfernen und das sieht schon wesentlich übersichtlicher aus. Sie haben nun fast die gleiche Ausgangslage, wie sie im ersten Teil dieser Trilogie existiert. Nur die Spalten von und bis sind hier vom Datentyp Text. Jetzt folgen diese Schritte:

  • Die beiden Spalten von und bis als Typ: Ganze Zahl umwandeln.
  • Spalte hinzufügen und Benutzerdefinierte Spalte anklicken.
  • Neuer Spaltenname: Werte und diese Formel eingeben: {[von]..[bis]}
  • Die neue Spalte Werte Doppelpfeil erweitern und Auf neue Zeilen ausweiten.
  • Kontrollieren Sie, ob erst einmal alles okay ist:

Das vorläufige Ergebnis ist so, wie es sein soll

  • Markieren Sie Präfix, Strg und Werte und per Rechtsklick in eine Überschrift Spalten zusammenführen. Kein Trennzeichen. Gerne Wert (Singular) als Überschrift wählen.
  • Die Spalten Wert, Variable 1 und Variable 2 in dieser Reihenfolge markieren.
  • Rechtsklick in eine der markierten Überschriften und Andere Spalten entfernen.
  • Schließen & laden oder Schließen & laden in… und das Werk ist getan. 🙂

Erst einmal zumindest… Denn bis zu diesem Zeitpunkt ist die Vorgehensweise zwar eine ganz andere als im ersten Teil gezeigt, aber das Ergebnis ist “natürlich” gleich, denn die Ausgangslage ist ja auch die selbe. Sie könnten also nach Belieben frei entscheiden, welchen Weg Sie gehen wollen. Zumindest, sofern Ihre Excel-Version mindestens 2019 oder 365 ist..

Ganz zu Beginn hatte ich Ihnen ja schon “angedroht”, dass der Präfix nicht immer nur aus 1 alphanumerischen Zeichen bestehen muss, es können auch einmal mehr sein. Die Gelegenheit ist gut, Sie befinden sich ja gerade in der Arbeitsmappe und können somit auch die Quelldaten verändern.

Erweitern Sie die Tabelle Ist um 1 Zeile und tragen Sie einen (beispielsweise) 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 beiden andern Spalten.

Ein Rechtsklick in die durch Power Query erstellte Tabelle und Aktualisieren. Herunterscrollen zum Ende der Tabelle und ich bin zufrieden. 😎 Sie hoffentlich auch, sonst “dürfen” Sie gerne auf die Fehlersuche gehen. 💡

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00 € freuen …


Dieses Thema umfasst mehrere Teile:

Teil 1 Gilt für alle PQ-Versionen. Voraussetzung ist aber, dass ein vorhandenes Präfix maximal 1 Zeichen lang ist.
Teil 2 Erfordert Excel >=2019 oder 365. Das Präfix hat eine beliebige Länge und kann recht problemlos separiert werden.
Teil 3 Präfix hat beliebige Länge, beliebige PQ-Version. Deutlich aufwendiger als mit den neueren Versionen (siehe Teil 2) aber dennoch zielführend.
$/$$ Außer
Konkurrenz
Gleiche Voraussetzungen wie in Teil 3. Mehrere Möglichkeiten, auf kürzeren Wegen, mit weniger Schritten zum gewünschten Ziel zu gelangen. Viel M-Code.
Dieser Beitrag wurde unter Foren-Q&A, PQ-Formeln (Sprache M), Spalten bearbeiten, Text-Behandlung, {Liste} abgelegt und mit , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.