PQ: Dubletten ab 2. Eintrag in der nebenstehenden Spalte markieren

In ei­nem Fo­rum wur­de die Fra­ge ge­stellt, wie eine grö­ße­re Lis­te mit Zell-Ein­trä­gen so gekennze­ich­net wer­den kann, dass in ein­er Nach­barspalte ab dem 2. Ein­trag ein x einge­tra­gen wird; der ers­te Wert in ein­er Lis­te soll also in der Ne­ben­spal­te leer blei­ben oder ei­nen Leer­String "" enthal­ten. Die ur­sprüng­li­che Rei­hen­folge der Dat­en soll im End­ef­fekt erhal­ten blei­ben. Im Fo­rum wur­den unter­schiedliche Vari­anten der ZÄH­LEN­WENN()-Funk­tion vorgeschla­gen, ich habe mich aber ent­schlos­sen, das gan­ze mit Pow­er Que­ry zu lö­sen.

Wenn Sie mein Bei­spiel nachvol­lziehen wol­len, dann la­den Sie ide­al­er­weise die­se Da­tei von un­se­rem Serv­er her­un­ter. Sie erken­nen, dass in der einzi­gen Spal­te 50 Text-Wer­te enthal­ten sind. Die­se sol­len der Vor­gabe ge­mäß in der Nach­barspalte mit ei­nem x verse­hen wer­den, so­fern der ent­spre­chen­de Wert zum zweit­en, drit­ten, vier­ten … mal aufge­führt ist. Wie üb­lich begin­nen Sie da­mit, die Dat­en in eine for­matierte Ta­bel­le umzuwan­deln. Das geht recht gut, wenn eine be­lie­bi­ge Zel­le in den Dat­en mar­kiert ist und dann StrgT oder StrgL. An­schlie­ßend Dat­en | Aus Ta­bel­le (in äl­te­ren Ver­sio­nen Pow­er Que­ry | Von Ta­bel­le) und es öff­net sich der Pow­er Que­ry-Edi­tor mit den entsprechen­den im­por­tier­ten Dat­en.

Im ers­ten Schritt wer­den Sie da­für sor­gen, dass auf je­den Fall die­se ur­sprüng­li­che, jet­zt exis­tie­ren­de Rei­hen­folge der Dat­en wieder­hergestellt wer­den kann. Dazu wech­seln Sie im Menü zum Reg­is­ter Spal­te hinzufü­gen und wäh­len dort in der Grup­pe All­ge­mein den Ein­trag Indexs­palte. Un­ver­züg­lich wird eine neue Spal­te mit der Über­schrift In­dex und ein­er mit 0 begin­nen­den fort­laufend­en Num­merierung er­stellt.

Der näch­ste Schritt ist auf den ers­ten Blick vielle­icht nicht so ganz nachvol­lziehbar, aber ich ver­sichere Ih­nen, das ist ein gut funk­tion­ieren­der und zielführen­der Weg. 😉 Mar­kie­ren Sie Spal­te1 durch ei­nen Recht­sklick in die Über­schrift und wäh­len Sie im Kon­textmenü Grup­pieren nach … Ge­ben Sie bei Neu­er Spal­tenname das Wort Data ein und wäh­len Sie an­schlie­ßend bei Vor­gang den Ein­trag Alle Zei­len. An­schlie­ßend mit OK bestäti­gen. Das Ausse­hen än­dert sich, denn die Spal­te In­dex gibt es nicht mehr und da­für ist an der Stel­le eine Spal­te mit der Über­schrift Data und jede ein­zel­ne Zel­le hat den In­halt Ta­ble:

Die neu gener­ierte Spal­te ‘Data’

Im recht­en Seit­en­fen­ster erken­nen sie als let­zten Ein­trag Grup­pierte Zei­len. Im­mer noch im Reg­is­ter Spal­te hinzufü­gen Klick­en Sie in der Grup­pe All­ge­mein auf Be­nut­zer­de­fi­nier­te Spal­te. Es öff­net sich ein Dialogfen­ster wo sie bei Neu­er Spal­tenname den Text Du­blet­te ein­tra­gen. In das gro­ße Feld Be­nut­zer­de­fi­nier­te Spal­tenformel tra­gen Sie die­se For­mel ohne das füh­ren­de Gle­ich­heit­sze­ichen ein:
= Table.AddIndexColumn([Data], "Gruppen-Index", 1, 1)
wo­bei sie den Spal­tenna­men [Data] mit­samt den eck­i­gen Klam­mern durch ei­nen Dop­pelk­lick auf den Ein­trag im recht­en Kas­ten des Fen­sters aus­wäh­len kön­nen:

Der Dia­log mit dem Spal­tenna­men und der einge­tra­ge­nen For­mel

Nach dem schlie­ßen des Fen­sters ist wie­der­um eine neue Spal­te mit der Über­schrift Du­blet­te er­stellt wor­den und auch hier ist der In­halt jed­er Zei­le Ta­ble (Ta­bel­le). Klick­en Sie in die­ser Spal­te in der Über­schrift auf das Erweit­ern-Sym­bol (den Dop­pelpfeil) und es öff­net sich die­ser Dia­log:

Der unverän­derte Erweit­ern-Dia­log

Ent­fer­nen Sie die Häk­chen bei Spal­te1 und bei Ur­sprüng­li­chen Spal­tenna­men als Prä­fix ver­wen­den. Nach dem schlie­ßen die­ses Fen­sters wird sich die Ab­fra­ge im Edi­tor so dar­stel­len:

Zei­len sind nun nach Spal­te1 zusam­menge­fasst

An der Spal­te In­dex erken­nen Sie, dass die ur­sprüng­li­che Rei­hen­folge durcheinan­derge­bracht wor­den ist. Sie se­hen aber auch, dass in Spal­te1 die iden­tis­chen Ein­träge zusam­menge­fasst wor­den sind und in der Spal­te Grup­pen-Index eine fort­laufende Num­merierung geschaf­fen wor­den ist.

Erweit­ern Sie nun die Spal­te In­dex durch ei­nen Klick auf das Drei­eck und sor­tie­ren Sie den In­halt auf­steigend. Da­durch wird natür­lich die ge­sam­te Rei­hen­folge in die ur­sprüng­li­che Ord­nung zurück­ver­set­zt. Zur Verdeut­lichung habe ich in die­sem Screen­shot für die ers­ten bei­den Dublet­ten eine Mar­kie­rung einge­fügt:

Die Mar­kie­rung zeigt auf, dass der ‘Grup­pen-Index’ kor­rekt ist

Sie erken­nen, dass auch die automa­tis­che Num­merierung in der Spal­te Grup­pen-Index dem n-ten auf­tre­ten des jew­eili­gen Wer­tes in Spal­te1 ent­spricht. Und jet­zt kön­nen Sie schnur­stracks auf das Ziel zus­teuern. Sie müs­sen „nur” da­für sor­gen, dass in ein­er neu­en Spal­te über­all dort ein x einge­tra­gen wird, wo in der Spal­te Grup­pen-Index ein Wert >1 enthal­ten ist; alle an­de­ren Fel­der die­ser Spal­te sol­len leer blei­ben.

Es liegt nahe, dass sie nun eine weit­ere Spal­te er­stel­len wer­den. Da es sich ja um eine ein­fache Wenn-Bedin­gung han­delt, Klick­en Sie auf Bed­ingte Spal­te. Ge­ben Sie dort die in der fol­gen­den Abbil­dung ge­zeig­ten Wer­te ein:

So kom­men Sie zum kor­rek­ten Ergeb­nis

Et voi­là, nach dem schlie­ßen des Fen­sters ste­ht beim jew­eils ers­ten Auf­tauchen des Wer­tes in Spal­te1 der Wert null und beim zweit­en, drit­ten, … Ein­trag wie gewün­scht ein x. Mar­kie­ren Sie nun Spal­te1, Strg und Dup­likat. Recht­sklick in eine der bei­den mar­kier­ten Über­schriften und An­de­re Spal­ten ent­fer­nen. Da­mit ist das Ergeb­nis an­schlie­ßend so wie gewün­scht. Um die Ab­fra­ge in ei­nen Tabel­len­blatt zu spe­ich­ern, Klick­en Sie entwed­er im Reg­is­ter Home auf das Sym­bol Schlie­ßen & la­den oder Klick­en Sie auf den Text dar­un­ter und wäh­len Sie im Pull­down-Menü Schlie­ßen & la­den in… Im erst­ge­nan­nten Fall wird in ei­nem neu­en Arbeits­blatt eine Ta­bel­le mit die­sem bei­den Spal­ten er­stellt, wenn Sie auf den Text gek­lickt ha­ben kön­nen Sie die Posi­tion bes­tim­men, wo die Ta­bel­le in ein auszuwäh­len­des Arbeits­blatt gespe­ichert wer­den soll.

Die Auf­gabe ist ge­löst. Und Sie ha­ben natür­lich so­fort ge­merkt, dass all jene Fel­der, wo in der Ab­fra­ge ein Wert null einge­tra­gen war, hier in der Ex­cel-Ta­bel­le wirk­lich leer sind. Sie soll­ten trotz die­ses Er­fol­ges auch ein­mal die Excel-Funk­tion ZÄH­LEN­WENN() als Ver­gle­ich ver­wen­den. Vielle­icht möcht­en Sie auch eine VBA-Rou­tine dazu schrei­ben. Und natür­lich kön­nen Sie auch im Fo­rum nach­se­hen, wel­che Lösun­gen dort erar­beit­et wor­den sind. Und selb­stre­dend noch ein­mal der Hin­weis, dass Pow­er Que­ry in die­sem Fall sei­ne Stär­ken erst dann rich­tig aus­spielt, wenn sehr vie­le Dat­en vorhan­den sind oder sich die Daten­ba­sis tag­täg­lich än­dert. 😎 

▲ nach oben …