Zeilen in definierter Zahl wiederholen

Xtract: Mit Pow­er Que­ry eine Zei­le in definiert­er An­zahl wieder­holen bzw. mit ei­nem definier­baren Start­wert bis zum Endw­ert in 1^er-Schrit­ten hoch­zäh­len.

Die­se Ex­cel-Ta­bel­le dient als Ba­sis für den Liefer­schein. Ver­schiedene Pro­duk­te der Liefer­ung he­ben sich da­durch her­vor, dass je­des ein­zel­ne Teil eine indi­vidu­elle Seri­en­num­mer hat, wel­che auch im Liefer­schein no­tiert wer­den muss. Für die­se Exem­plare soll­ten die jew­eilige Zei­le, jed­er Daten­satz so oft wieder­holt wer­den, bis die An­zahl der geliefer­ten Stü­cke in der An­zahl des pas­sen­den Daten­satzes erre­icht ist. Wer­den also beispiel­sweise drei Fest­plat­ten ver­sandt, dann soll die Zei­le um zwei weit­ere, gle­ichar­tige Zei­len er­gänzt wer­den. Gibt es für den Ar­ti­kel kei­ne Seri­en­num­mer, dann bleibt es bei der ei­nen Zei­le, wie auch bei den Ar­ti­keln, wo nur ein Stück ge­lie­fert wor­den ist, selb­st wenn eine S/N einge­tra­gen wer­den soll.

Schnell und Ef­fek­tiv

Wech­seln Sie nach dem Im­port der Ta­bel­le in den Pow­er Que­ry-Edi­tor in das Reg­is­ter Spal­te hinzufü­gen und Klick­en Sie auf das Sym­bol Be­nut­zer­de­fi­nier­te Spal­te. Im Dia­log ver­ge­ben Sie als Neu­er Spal­tenname beispiel­sweise Lis­te. In dem gro­ßen Textbere­ich Be­nut­zer­de­fi­nier­te Spal­tenformel: ge­ben Sie die­se For­mel ein, um die Lis­te zu gner­ieren:
{1..[Lie­fe­rung]}.
Die äu­ße­ren Klam­mern sind ge­schweif­te Klam­mern (Alt­Gr7 bzw. Alt­Gr0), nicht die üb­li­chen run­den Klam­mern. Den Spal­tenna­men [Liefer­ung] kön­nen Sie am leicht­esten über­neh­men, wenn sie ei­nen Dop­pelk­lick auf den Ein­trag im Kas­ten Ver­füg­bare Spal­ten: durch­führen. Selb­stre­dend kön­nen Sie hier auch bei Be­darf ei­nen fes­ten Wert ein­tra­gen. 😎 Und das Gan­ze stellt sich nun so dar:

Er­stel­len ein­er Be­nut­zer­de­fi­nier­ten Spal­te mit die­ser For­mel

Nach ei­nem Klick auf die Schalt­fläche OK erken­nen Sie, dass eine neue Spal­te mit der Über­schrift Lis­te gener­iert wur­de. Der In­halt jed­er einzel­nen Zel­le ist List. Erweit­ern Sie die­se Spal­te und durch ei­nen Klick auf den Dop­pelpfeil in der Über­schrift und bestäti­gen Sie die Vor­gabe Auf Neue Zei­len ausweit­en mit ei­nem Maus­klick.

Sie wer­den erken­nen, dass das des Gu­ten doch entsch­ieden zu viel war. Schlie­ß­lich soll­ten ja nur jene Zei­len ver­viel­facht wer­den, wo eine Seri­en­num­mer einge­tra­gen wer­den soll. Alle an­de­ren Zei­len sol­len nur ein Mal exis­tie­ren. Also wer­den Sie erst ein­mal im recht­en Seit­en­fen­ster die bei­den let­zten Schrit­te (Hinzuge­fügte be­nut­zer­de­fi­nier­te… so­wie Erweit­erte Lis­te) ent­fer­nen. Da­mit ist jet­zt wie­der der Zus­tand her­ge­stellt, wie er di­rekt nach dem Im­port war.

Hin­weis: Die­ses hier ge­schil­der­te Vorge­hen ist natür­lich im­mer dann kurz und knack­ig und auch vol­lkom­men kor­rekt, wenn jed­er in der vorhan­de­nen Zah­len ent­spre­chend der An­ga­ben in ein­er de­fi­nier­ten Spal­te wieder­holt wer­den soll. Wenn also in je­dem Fall der Daten­satz auf die in ein­er Spal­te genan­nte An­zahl mul­ti­pliziert wer­den soll, ohne jede Ein­schränkung.

Da­mit im End­ef­fekt auch jede Zei­le min­destens 1 Mal an­ge­zeigt wird, wer­den Sie im näch­sten Schritt eine In­dex-Spal­te ein­fü­gen. Spal­te hinzufü­gen | Indexs­palte. Er­stel­len Sie nun ein Dup­likat die­ser Ab­fra­ge, in­dem Sie beispiel­sweise über Start | Ver­wal­ten | Du­pli­zie­ren ge­hen. Beacht­en Sie bei die­ser Gele­gen­heit, dass sich das lin­ke Seit­en­fen­ster erweit­ert und bei­de Abfra­gen dort aufge­lis­tet sind. In die­ser Ko­pie fil­tern Sie nun S/N auf TRUE und Liefer­ung über Zahlen­fil­ter | Grö­ßer als…  0. Und mit die­sen 3 verbleiben­den Zei­len ver­fahren Sie ge­nau so, wie oben be­schrie­ben und schon ein­ge­übt: Spal­te hinzufü­gen | Be­nut­zer­de­fi­nier­te Spal­te und beispiel­sweise Lis­te als Über­schrift so­wie
{1..[Lieferung]}
als For­mel ein­ge­ben, an­schlie­ßend mit OK bestäti­gen. Die Spal­te Lis­te wer­den Sie wie ge­habt durch Klick auf die Schalt­fläche erweit­ern. Die­se Que­ry (Ab­fra­ge) beste­ht nun aus 8 Zei­len.

Wech­seln Sie jet­zt zur Ab­fra­ge Ta­bel­le1. Am ein­fach­sten ist das ge­wiss, wenn Sie im lin­ken Seit­en­fen­ster auf den entsprechen­den Ein­trag Klick­en. Start | Kom­binieren | Abfra­gen zusam­men­führen und wäh­len Sie im klei­nen Textfen­ster mit dem Drop­Down-Pfeil den Ein­trag Ta­bel­le1 (2). Die ers­ten Zei­len der ex­pan­dier­ten Ab­fra­ge wer­den im un­te­ren Kas­ten einge­blendet. Klick­en Sie nun in bei­den Ta­bel­len in die Spal­te In­dex und das Ergeb­nis stellt sich so dar:

Zusam­men­führen zwei­er Abfra­gen

Join-Art kann so blei­ben, also OK. Eine weit­ere Spal­te, ähn­lich der zu­vor erstell­ten Lis­te, wur­de gener­iert. Erweit­ern Sie die Über­schrift Ta­bel­le1 (2) und Klick­en Sie auf (Alle Spal­ten aus­wäh­len) so­wie Ur­sprüng­li­chen Spal­tenna­men als Prä­fix ver­wen­den. Da­mit wer­den alle Häk­chen ent­fer­nt. Mar­kie­ren Sie nun ei­nen beliebi­gen Ein­trag, beispiel­sweise Lis­te. An­schlie­ßend OK. Die Ab­fra­ge wird nun ge­nau so erweit­ert, wie es sin­nvoll und prinzip­iell auch so gewün­scht ist. Die Spal­ten In­dex so­wie Lis­te wer­den nun nicht mehr ge­braucht, lö­schen Sie die­se. Das geht auch sehr schön nach Mar­kie­rung der Über­schriften mit Entf.

Reizt es Sie, auch in PQ den End­preis auszurech­nen? Okay, der ge­hört prinzip­iell nicht in ei­nen Liefer­schein hin­ein, aber das gilt dann na­tur­ge­mäß auch für den Einzel­preis. Der Weg zum Ziel geht sog­ar ganz ohne (ein­zu­ge­be­ne) For­mel. Und zwar so:

• Mar­kie­ren Sie die Spal­te Einzel­preis
• Spal­te hinzufü­gen | Stan­dard | Mul­ti­plizieren
• Erweit­ern Sie Wert und wäh­len Sie den un­te­ren Ein­trag:

Der Mul­ti­p­lika­tor soll aus ein­er Spal­te gele­sen wer­den

• Im Drop­Down rechts der Schalt­fläche wäh­len Sie das Feld Liefer­ung und OK.
• Än­dern Sie die Über­schrift der Spal­te Mul­ti­p­lika­tion auf Gesamt­preis.
• Mar­kie­ren Sie nun noch die Spal­ten Einzel­preis und Gesamt­preis, Recht­sklick in eine der bei­den mar­kier­ten Über­schriften und Typ än­dern | Wäh­rung.

Da­mit ist die Auf­gabe kom­plett er­füllt. Start | Schlie­ßen und la­den und Pow­er Que­ry er­stellt auf ei­nem neu­en Blatt wird eine Ta­bel­le mit den ak­tu­el­len Dat­en der Que­ry er­stellt. Bei Be­darf kön­nen Sie hier auch die bei­den Preis-Spal­ten als Wäh­rung for­matieren und vielle­icht auch ver­steck­en.

▲ nach oben …

Der „klas­si­sche” Weg

Ide­al­er­weise ko­pie­ren Sie die ur­sprüng­li­che Ta­bel­le (A1:E9), er­stel­len eine neue Ex­cel-Map­pe, fü­gen in A1 den In­halt der Zwis­chen­ablage ein und im­por­tie­ren nun die­se Ta­bel­le. Alter­na­tiv spe­ich­ern und schlie­ßen Sie die­se Map­pe und la­den noch ein­mal die auf un­se­rem Serv­er lie­gen­de Da­tei Zei­len_­ver­viel­fa­chen.xlsx.

Wie ge­habt (und weit­er oben be­schrie­ben) im­por­tie­ren Sie die Ta­bel­le in den Pow­er Que­ry-Edi­tor und fü­gen Sie eine In­dex-Spal­te ein. Dann kön­nten Sie wie­der­um ein Dup­likat der Ab­fra­ge er­stel­len oder aber Sie erweit­ern den lin­ken Seit­en­bere­ich durch Klick auf den Text Abfra­gen, Recht­sklick auf Ta­bel­le1 und ein Klick auf Ver­weis. Wie auch bei ei­nem Dup­likat wird eine Ko­pie der Ab­fra­ge er­stellt. Was Ih­nen vielle­icht auf­fällt: Im recht­en Seit­en­fen­ster wer­den nicht die ge­sam­ten bish­eri­gen Schrit­te aufge­führt, son­dern nur der Ein­trag Quel­le. Das hat un­ter An­de­rem den Vor­teil, dass alle Änderun­gen, die Sie spä­ter vielle­icht ein­mal in der Ursprungsta­belle vor­neh­men, automa­tisch in die­se Ko­pie über­nom­men wer­den. Okay, Sie wer­den die­se spe­zi­el­le Funk­tion­al­ität der Ver­weis-Kopie hier nicht nut­zen, aber es ist gut, die­sen Unter­schied zu ken­nen. Der Klar­heit we­gen: Den Ver­weis an sich sol­len Sie statt des Dup­likats ver­wen­den!

In Ta­bel­le1 (2) wer­den Sie nun wie­der die Spal­te S/N nach dem Wahr­heits­wert TRUE fil­tern und auch die 0-Liefer­un­gen aus­blenden. Bei der Gele­gen­heit kön­nten Sie auch gle­ich die verbleiben­den Fel­der der Seri­en­num­mer „lee­ren”, in­dem Sie beispiel­sweise ei­nen Recht­sklick in die Über­schrift S/N durch­führen und im Kon­textmenü den Ein­trag Wer­te erset­zen… anklick­en und hier TRUE durch null erset­zen; „null” in Klein­buch­staben und als Text (ohne die Anführungsze­ichen). Und nun kön­nen in die­se lee­ren Zel­len nach dem Spe­ich­ern in Ex­cel die Seri­en­num­mern von Hand oder per Scan­ner einge­tra­gen wer­den.

Jet­zt aber kommt der wirk­lich an­de­re Teil 😉 . Spal­te hinzufü­gen | Be­nut­zer­de­fi­nier­te Spal­te und als Über­schrift ger­ne wie­der Lis­te. Die For­mel aber ist eine an­de­re:
List.Range({1..[Lieferung]},0)

Der in­ne­re Teil der For­mel wird Ih­nen bekan­nt vorkom­men, er ent­spricht dem vorheri­gen Mod­ell. Das List.​Range() ent­stammt dem Fun­dus der Funk­tio­nen aus der Spra­che M, wel­che spe­zi­ell für Pow­er Que­ry geschaf­fen wur­de. Die als zwei­tes Argu­ment an­ge­ge­be­ne Null (0) ist der Wert, der als Start-Off­set ge­nutzt wird. Im Klar­text: Wie vie­le Schrit­te sol­len beim Start über­sprun­gen wer­den? Das macht die­se et­was aufwändi­gere For­mel aber auch flex­i­bler. Und bei die­ser Gele­gen­heit der Hin­weis, dass die Doku­men­ta­tion der Funk­tio­nen meis­tens recht gut ist; allerd­ings nur in good old eng­lish. 🙄 

Die rest­li­chen Schrit­te sind erst ein­mal, wie ge­habt:

• Die Spal­te Lis­te erweit­ern
• Ta­bel­le1 aus­wäh­len
• Start | Kom­binieren | Abfra­gen zusam­men­führen
• Ta­bel­le1 (2) als zwei­te Ta­bel­le aus­wäh­len
• Wun­dern …

War­um ist kei­ne Vor­schau ver­füg­bar?

Im un­te­ren Kas­ten wird nichts an­ge­zeigt und da­mit ist natür­lich auch kei­ne Verknüp­fung zwei­er Ta­bel­len mög­lich. Der Grund da­für ist, dass die ko­pier­te Que­ry ein Ver­weis und kein Dup­likat ist. Der Preis der Flex­i­bil­ität … 🙁

Aber es gibt (natür­lich) den­noch eine Lö­sung des Prob­lems. Schlie­ßen Sie erst ein­mal den Dia­log per Ab­bre­chen.  Jet­zt wie­der­um im Men­üband Kom­binieren | Abfra­gen zusam­men­führen erweit­ern und Abfra­gen als neue Ab­fra­ge zusam­men­führen. Wie auch im ers­ten Bei­spiel Ta­bel­le1 (2) als zu ver­Link­ende Que­ry hinzufü­gen und schon ha­ben Sie wie­der das er­war­te­te Bild auf dem Schirm. 🙂

Wie­der­um die bei­den Spal­ten In­dex mar­kie­ren und die­sen Dia­log durch Klick auf OK schlie­ßen. Im Edi­tor die let­zte Spal­te Ta­bel­le1 (2)  durch Klick auf den Dop­pelpfeil erweit­ern. Und auch hier bie­tet es sich an, das Feld Lis­te als einzi­gen Ein­trag beste­hen, ange­hakt zu las­sen (es kann prinzip­iell aber auch je­des be­lie­bi­ge an­de­re Feld sein, aber se­hen Sie selb­st…).

So weit, so gut. Erst ein­mal, denn Sie wer­den sich vielle­icht fra­gen, war­um in der Spal­te S/N wie­der­um durch­gän­gig TRUE drin ste­ht, ob­wohl Sie vor weni­gen Mi­nu­ten doch die­sen Wahr­heits­wert durch null erset­zt ha­ben. Nun ja, Sie ha­ben die Erset­zung in der Ab­fra­ge Ta­bel­le1 (2) vorgenom­men, was Sie hier se­hen, ist die erweit­erte Ab­fra­ge Ta­bel­le1. Klick­en Sie im recht­en Seit­en­fen­ster in der Zei­le Erweit­erte Ta­bel­le1 (2) auf das Zah­n­rad-Sym­bol (rechts) und es öff­net sich wie­der­um der Dia­log, wo sie die anzuzeigen­den Fel­der beim erweit­ern aus­wer­ten kön­nen. Lö­schen Sie hier das Häk­chen bei Lis­te und set­zen Sie ei­nen Hak­en bei S/N. Jet­zt OK und die let­zte Spal­te stellt sich et­was an­ders dar:

So soll sich die Seri­en­num­mer dar­stel­len…

Da in ein­er Ta­bel­le (und nichts an­de­res ist auch die­se Ab­fra­ge) kei­ne 2 Spal­ten den iden­tis­chen Na­men/Über­schrift ha­ben dür­fen, hat Pow­er Que­ry automa­tisch den Zu­satz .1 an den ur­sprüng­li­chen Spal­tenna­men ange­fügt. Ide­al­er­weise lö­schen Sie nun die 2. Spal­te S/N, ver­schieben die let­zte Spal­te mit der Maus auf die 2. Posi­tion und än­dern die Über­schrift wie­der auf S/N. Im let­zten Schritt Lö­schen Sie die Spal­te In­dex, sie wird nicht mehr be­nö­tigt. Schlie­ßen & la­den und Ab­fra­ge wird als nor­maler Ex­cel-Ta­bel­le in ein neu­es Tabel­len­blatt ge­schrie­ben.

Es gibt noch weit­ere Möglichkeit­en, solch eine Lis­te zu erweit­ern. Für den Nor­mal­ge­brauch rei­chen die­se bei­den Möglichkeit­en je­doch vol­lkom­men aus und ich möch­te es auch da­bei be­las­sen; schlie­ß­lich ist die­ser Bei­trag für Ein­steiger in Sa­chen Pow­er Que­ry ge­dacht.

▲ 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. 3,00 € freu­en …