Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
Xtract: Mit Power Query eine Zeile in definierter Anzahl wiederholen bzw. mit einem definierbaren Startwert bis zum Endwert in 1er-Schritten hochzählen.
Diese Excel-Tabelle dient als Basis für den Lieferschein. Verschiedene Produkte der Lieferung heben sich dadurch hervor, dass jedes einzelne Teil eine individuelle Seriennummer hat, welche auch im Lieferschein notiert werden muss. Für diese Exemplare sollten die jeweilige Zeile, jeder Datensatz so oft wiederholt werden, bis die Anzahl der gelieferten Stücke in der Anzahl des passenden Datensatzes erreicht ist. Werden also beispielsweise drei Festplatten versandt, dann soll die Zeile um zwei weitere, gleichartige Zeilen ergänzt werden. Gibt es für den Artikel keine Seriennummer, dann bleibt es bei der einen Zeile, wie auch bei den Artikeln, wo nur ein Stück geliefert worden ist, selbst wenn eine S/N eingetragen werden soll.
Schnell und Effektiv
Wechseln Sie nach dem Import der Tabelle in den Power Query-Editor in das Register Spalte hinzufügen und Klicken Sie auf das Symbol Benutzerdefinierte Spalte. Im Dialog vergeben Sie als Neuer Spaltenname beispielsweise Liste. In dem großen Textbereich Benutzerdefinierte Spaltenformel: geben Sie diese Formel ein, um die Liste zu gnerieren:
{1..[Lieferung]}.
Die äußeren Klammern sind geschweifte Klammern (AltGr7 bzw. AltGr0), nicht die üblichen runden Klammern. Den Spaltennamen [Lieferung] können Sie am leichtesten übernehmen, wenn sie einen Doppelklick auf den Eintrag im Kasten Verfügbare Spalten: durchführen. Selbstredend können Sie hier auch bei Bedarf einen festen Wert eintragen. 😎 Und das Ganze stellt sich nun so dar:
Erstellen einer Benutzerdefinierten Spalte mit dieser Formel
Nach einem Klick auf die Schaltfläche OK erkennen Sie, dass eine neue Spalte mit der Überschrift Liste generiert wurde. Der Inhalt jeder einzelnen Zelle ist List. Erweitern Sie diese Spalte und durch einen Klick auf den Doppelpfeil 
in der Überschrift und bestätigen Sie die Vorgabe Auf Neue Zeilen ausweiten mit einem Mausklick.
Sie werden erkennen, dass das des Guten doch entschieden zu viel war. Schließlich sollten ja nur jene Zeilen vervielfacht werden, wo eine Seriennummer eingetragen werden soll. Alle anderen Zeilen sollen nur ein Mal existieren. Also werden Sie erst einmal im rechten Seitenfenster die beiden letzten Schritte (Hinzugefügte benutzerdefinierte… sowie Erweiterte Liste) entfernen. Damit ist jetzt wieder der Zustand hergestellt, wie er direkt nach dem Import war.
Hinweis: Dieses hier geschilderte Vorgehen ist natürlich immer dann kurz und knackig und auch vollkommen korrekt, wenn jeder in der vorhandenen Zahlen entsprechend der Angaben in einer definierten Spalte wiederholt werden soll. Wenn also in jedem Fall der Datensatz auf die in einer Spalte genannte Anzahl multipliziert werden soll, ohne jede Einschränkung.
Damit im Endeffekt auch jede Zeile mindestens 1 Mal angezeigt wird, werden Sie im nächsten Schritt eine Index-Spalte einfügen. Spalte hinzufügen | Indexspalte. Erstellen Sie nun ein Duplikat dieser Abfrage, indem Sie beispielsweise über Start | Verwalten | Duplizieren gehen. Beachten Sie bei dieser Gelegenheit, dass sich das linke Seitenfenster erweitert und beide Abfragen dort aufgelistet sind. In dieser Kopie filtern Sie nun S/N auf TRUE und Lieferung über Zahlenfilter | Größer als… 0. Und mit diesen 3 verbleibenden Zeilen verfahren Sie genau so, wie oben beschrieben und schon eingeübt: Spalte hinzufügen | Benutzerdefinierte Spalte und beispielsweise Liste als Überschrift sowie
{1..[Lieferung]}
als Formel eingeben, anschließend mit OK bestätigen. Die Spalte Liste werden Sie wie gehabt durch Klick auf die Schaltfläche erweitern. Diese Query (Abfrage) besteht nun aus 8 Zeilen.
Wechseln Sie jetzt zur Abfrage Tabelle1. Am einfachsten ist das gewiss, wenn Sie im linken Seitenfenster auf den entsprechenden Eintrag Klicken. Start | Kombinieren | Abfragen zusammenführen und wählen Sie im kleinen Textfenster mit dem DropDown-Pfeil den Eintrag Tabelle1 (2). Die ersten Zeilen der expandierten Abfrage werden im unteren Kasten eingeblendet. Klicken Sie nun in beiden Tabellen in die Spalte Index und das Ergebnis stellt sich so dar:
Zusammenführen zweier Abfragen
Join-Art kann so bleiben, also OK. Eine weitere Spalte, ähnlich der zuvor erstellten Liste, wurde generiert. Erweitern Sie die Überschrift Tabelle1 (2) und Klicken Sie auf (Alle Spalten auswählen) sowie Ursprünglichen Spaltennamen als Präfix verwenden. Damit werden alle Häkchen entfernt. Markieren Sie nun einen beliebigen Eintrag, beispielsweise Liste. Anschließend OK. Die Abfrage wird nun genau so erweitert, wie es sinnvoll und prinzipiell auch so gewünscht ist. Die Spalten Index sowie Liste werden nun nicht mehr gebraucht, löschen Sie diese. Das geht auch sehr schön nach Markierung der Überschriften mit Entf.
Reizt es Sie, auch in PQ den Endpreis auszurechnen? Okay, der gehört prinzipiell nicht in einen Lieferschein hinein, aber das gilt dann naturgemäß auch für den Einzelpreis. Der Weg zum Ziel geht sogar ganz ohne (einzugebene) Formel. Und zwar so:
- Markieren Sie die Spalte Einzelpreis
- Spalte hinzufügen | Standard | Multiplizieren
- Erweitern Sie Wert und wählen Sie den unteren Eintrag:
Der Multiplikator soll aus einer Spalte gelesen werden
- Im DropDown rechts der Schaltfläche wählen Sie das Feld Lieferung und OK.
- Ändern Sie die Überschrift der Spalte Multiplikation auf Gesamtpreis.
- Markieren Sie nun noch die Spalten Einzelpreis und Gesamtpreis, Rechtsklick in eine der beiden markierten Überschriften und Typ ändern | Währung.
Damit ist die Aufgabe komplett erfüllt. Start | Schließen und laden und Power Query erstellt auf einem neuen Blatt wird eine Tabelle mit den aktuellen Daten der Query erstellt. Bei Bedarf können Sie hier auch die beiden Preis-Spalten als Währung formatieren und vielleicht auch verstecken.
Der „klassische” Weg
Idealerweise kopieren Sie die ursprüngliche Tabelle (A1:E9), erstellen eine neue Excel-Mappe, fügen in A1 den Inhalt der Zwischenablage ein und importieren nun diese Tabelle. Alternativ speichern und schließen Sie diese Mappe und laden noch einmal die auf unserem Server liegende Datei Zeilen_vervielfachen.xlsx.
Wie gehabt (und weiter oben beschrieben) importieren Sie die Tabelle in den Power Query-Editor und fügen Sie eine Index-Spalte ein. Dann könnten Sie wiederum ein Duplikat der Abfrage erstellen oder aber Sie erweitern den linken Seitenbereich durch Klick auf den Text Abfragen, Rechtsklick auf Tabelle1 und ein Klick auf Verweis. Wie auch bei einem Duplikat wird eine Kopie der Abfrage erstellt. Was Ihnen vielleicht auffällt: Im rechten Seitenfenster werden nicht die gesamten bisherigen Schritte aufgeführt, sondern nur der Eintrag Quelle. Das hat unter Anderem den Vorteil, dass alle Änderungen, die Sie später vielleicht einmal in der Ursprungstabelle vornehmen, automatisch in diese Kopie übernommen werden. Okay, Sie werden diese spezielle Funktionalität der Verweis-Kopie hier nicht nutzen, aber es ist gut, diesen Unterschied zu kennen. Der Klarheit wegen: Den Verweis an sich sollen Sie statt des Duplikats verwenden!
In Tabelle1 (2) werden Sie nun wieder die Spalte S/N nach dem Wahrheitswert TRUE filtern und auch die 0-Lieferungen ausblenden. Bei der Gelegenheit könnten Sie auch gleich die verbleibenden Felder der Seriennummer „leeren”, indem Sie beispielsweise einen Rechtsklick in die Überschrift S/N durchführen und im Kontextmenü den Eintrag Werte ersetzen… anklicken und hier TRUE durch null ersetzen; „null” in Kleinbuchstaben und als Text (ohne die Anführungszeichen). Und nun können in diese leeren Zellen nach dem Speichern in Excel die Seriennummern von Hand oder per Scanner eingetragen werden.
Jetzt aber kommt der wirklich andere Teil 😉 . Spalte hinzufügen | Benutzerdefinierte Spalte und als Überschrift gerne wieder Liste. Die Formel aber ist eine andere:
List.Range({1..[Lieferung]},0)
Der innere Teil der Formel wird Ihnen bekannt vorkommen, er entspricht dem vorherigen Modell. Das List.Range() entstammt dem Fundus der Funktionen aus der Sprache M, welche speziell für Power Query geschaffen wurde. Die als zweites Argument angegebene Null (0) ist der Wert, der als Start-Offset genutzt wird. Im Klartext: Wie viele Schritte sollen beim Start übersprungen werden? Das macht diese etwas aufwändigere Formel aber auch flexibler. Und bei dieser Gelegenheit der Hinweis, dass die Dokumentation der Funktionen meistens recht gut ist; allerdings nur in good old english. 🙄
Die restlichen Schritte sind erst einmal, wie gehabt:
- Die Spalte Liste erweitern
- Tabelle1 auswählen
- Start | Kombinieren | Abfragen zusammenführen
- Tabelle1 (2) als zweite Tabelle auswählen
- Wundern …
Warum ist keine Vorschau verfügbar?
Im unteren Kasten wird nichts angezeigt und damit ist natürlich auch keine Verknüpfung zweier Tabellen möglich. Der Grund dafür ist, dass die kopierte Query ein Verweis und kein Duplikat ist. Der Preis der Flexibilität … 🙁
Aber es gibt (natürlich) dennoch eine Lösung des Problems. Schließen Sie erst einmal den Dialog per Abbrechen. Jetzt wiederum im Menüband Kombinieren | Abfragen zusammenführen 
erweitern und Abfragen als neue Abfrage zusammenführen. Wie auch im ersten Beispiel Tabelle1 (2) als zu verLinkende Query hinzufügen und schon haben Sie wieder das erwartete Bild auf dem Schirm. 🙂
Wiederum die beiden Spalten Index markieren und diesen Dialog durch Klick auf OK schließen. Im Editor die letzte Spalte Tabelle1 (2) durch Klick auf den Doppelpfeil erweitern. Und auch hier bietet es sich an, das Feld Liste als einzigen Eintrag bestehen, angehakt zu lassen (es kann prinzipiell aber auch jedes beliebige andere Feld sein, aber sehen Sie selbst…).
So weit, so gut. Erst einmal, denn Sie werden sich vielleicht fragen, warum in der Spalte S/N wiederum durchgängig TRUE drin steht, obwohl Sie vor wenigen Minuten doch diesen Wahrheitswert durch null ersetzt haben. Nun ja, Sie haben die Ersetzung in der Abfrage Tabelle1 (2) vorgenommen, was Sie hier sehen, ist die erweiterte Abfrage Tabelle1. Klicken Sie im rechten Seitenfenster in der Zeile Erweiterte Tabelle1 (2) auf das Zahnrad-Symbol 
(rechts) und es öffnet sich wiederum der Dialog, wo sie die anzuzeigenden Felder beim erweitern auswerten können. Löschen Sie hier das Häkchen bei Liste und setzen Sie einen Haken bei S/N. Jetzt OK und die letzte Spalte stellt sich etwas anders dar:
So soll sich die Seriennummer darstellen…
Da in einer Tabelle (und nichts anderes ist auch diese Abfrage) keine 2 Spalten den identischen Namen/Überschrift haben dürfen, hat Power Query automatisch den Zusatz .1 an den ursprünglichen Spaltennamen angefügt. Idealerweise löschen Sie nun die 2. Spalte S/N, verschieben die letzte Spalte mit der Maus auf die 2. Position und ändern die Überschrift wieder auf S/N. Im letzten Schritt Löschen Sie die Spalte Index, sie wird nicht mehr benötigt. Schließen & laden und Abfrage wird als normaler Excel-Tabelle in ein neues Tabellenblatt geschrieben.
Es gibt noch weitere Möglichkeiten, solch eine Liste zu erweitern. Für den Normalgebrauch reichen diese beiden Möglichkeiten jedoch vollkommen aus und ich möchte es auch dabei belassen; schließlich ist dieser Beitrag für Einsteiger in Sachen Power Query gedacht.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00 € freuen …