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

In einem Forum wurde die Frage gestellt, wie eine größere Liste 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 erste Wert in ein­er Liste soll also in der Nebenspalte leer bleiben oder einen Leer­String "" enthal­ten. Die ursprüngliche Rei­hen­folge der Dat­en soll im End­ef­fekt erhal­ten bleiben. Im Forum wur­den unter­schiedliche Vari­anten der ZÄHLENWENN()-Funk­tion vorgeschla­gen, ich habe mich aber entschlossen, das ganze mit Pow­er Query zu lösen.

Wenn Sie mein Beispiel nachvol­lziehen wollen, dann laden Sie ide­al­er­weise diese Datei von unserem Serv­er herunter. Sie erken­nen, dass in der einzi­gen Spalte 50 Text-Werte enthal­ten sind. Diese sollen der Vor­gabe gemäß in der Nach­barspalte mit einem x verse­hen wer­den, sofern der entsprechende Wert zum zweit­en, drit­ten, vierten … mal aufge­führt ist. Wie üblich begin­nen Sie damit, die Dat­en in eine for­matierte Tabelle umzuwan­deln. Das geht recht gut, wenn eine beliebige Zelle in den Dat­en markiert ist und dann StrgT oder StrgL. Anschließend Dat­en | Aus Tabelle (in älteren Ver­sio­nen Pow­er Query | Von Tabelle) und es öffnet sich der Pow­er Query-Edi­tor mit den entsprechen­den importierten Dat­en.

Im ersten Schritt wer­den Sie dafür sor­gen, dass auf jeden Fall diese ursprüngliche, jet­zt existierende Rei­hen­folge der Dat­en wieder­hergestellt wer­den kann. Dazu wech­seln Sie im Menü zum Reg­is­ter Spalte hinzufü­gen und wählen dort in der Gruppe All­ge­mein den Ein­trag Indexs­palte. Unverzüglich wird eine neue Spalte mit der Über­schrift Index und ein­er mit 0 begin­nen­den fort­laufend­en Num­merierung erstellt.

Der näch­ste Schritt ist auf den ersten Blick vielle­icht nicht so ganz nachvol­lziehbar, aber ich ver­sichere Ihnen, das ist ein gut funk­tion­ieren­der und zielführen­der Weg. 😉 Markieren Sie Spalte1 durch einen Recht­sklick in die Über­schrift und wählen Sie im Kon­textmenü Grup­pieren nach … Geben Sie bei Neuer Spal­tenname das Wort Data ein und wählen Sie anschließend bei Vor­gang den Ein­trag Alle Zeilen. Anschließend mit OK bestäti­gen. Das Ausse­hen ändert sich, denn die Spalte Index gibt es nicht mehr und dafür ist an der Stelle eine Spalte mit der Über­schrift Data und jede einzelne Zelle hat den Inhalt Table:

Die neu gener­ierte Spalte ‘Data’

Im recht­en Seit­en­fen­ster erken­nen sie als let­zten Ein­trag Grup­pierte Zeilen. Immer noch im Reg­is­ter Spalte hinzufü­gen Klick­en Sie in der Gruppe All­ge­mein auf Benutzerdefinierte Spalte. Es öffnet sich ein Dialogfen­ster wo sie bei Neuer Spal­tenname den Text Dublette ein­tra­gen. In das große Feld Benutzerdefinierte Spal­tenformel tra­gen Sie diese Formel ohne das führende Gle­ich­heit­sze­ichen ein:
= Table.AddIndexColumn([Data], "Gruppen-Index", 1, 1)
wobei sie den Spal­tenna­men [Data] mit­samt den eck­i­gen Klam­mern durch einen Dop­pelk­lick auf den Ein­trag im recht­en Kas­ten des Fen­sters auswählen kön­nen:

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

Nach dem schließen des Fen­sters ist wiederum eine neue Spalte mit der Über­schrift Dublette erstellt wor­den und auch hier ist der Inhalt jed­er Zeile Table (Tabelle). Klick­en Sie in dieser Spalte in der Über­schrift auf das Erweit­ern-Sym­bol (den Dop­pelpfeil) und es öffnet sich dieser Dia­log:

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

Ent­fer­nen Sie die Häkchen bei Spalte1 und bei Ursprünglichen Spal­tenna­men als Prä­fix ver­wen­den. Nach dem schließen dieses Fen­sters wird sich die Abfrage im Edi­tor so darstellen:

Zeilen sind nun nach Spalte1 zusam­menge­fasst

An der Spalte Index erken­nen Sie, dass die ursprüngliche Rei­hen­folge durcheinan­derge­bracht wor­den ist. Sie sehen aber auch, dass in Spalte1 die iden­tis­chen Ein­träge zusam­menge­fasst wor­den sind und in der Spalte Grup­pen-Index eine fort­laufende Num­merierung geschaf­fen wor­den ist.

Erweit­ern Sie nun die Spalte Index durch einen Klick auf das Dreieck und sortieren Sie den Inhalt auf­steigend. Dadurch wird natür­lich die gesamte Rei­hen­folge in die ursprüngliche Ord­nung zurück­ver­set­zt. Zur Verdeut­lichung habe ich in diesem Screen­shot für die ersten bei­den Dublet­ten eine Markierung einge­fügt:

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

Sie erken­nen, dass auch die automa­tis­che Num­merierung in der Spalte Grup­pen-Index dem n-ten auftreten des jew­eili­gen Wertes in Spalte1 entspricht. Und jet­zt kön­nen Sie schnurstracks auf das Ziel zus­teuern. Sie müssen „nur” dafür sor­gen, dass in ein­er neuen Spalte über­all dort ein x einge­tra­gen wird, wo in der Spalte Grup­pen-Index ein Wert >1 enthal­ten ist; alle anderen Felder dieser Spalte sollen leer bleiben.

Es liegt nahe, dass sie nun eine weit­ere Spalte erstellen wer­den. Da es sich ja um eine ein­fache Wenn-Bedin­gung han­delt, Klick­en Sie auf Bed­ingte Spalte. Geben Sie dort die in der fol­gen­den Abbil­dung gezeigten Werte ein:

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

Et voilà, nach dem schließen des Fen­sters ste­ht beim jew­eils ersten Auf­tauchen des Wertes in Spalte1 der Wert null und beim zweit­en, drit­ten, … Ein­trag wie gewün­scht ein x. Markieren Sie nun Spalte1, Strg und Dup­likat. Recht­sklick in eine der bei­den markierten Über­schriften und Andere Spal­ten ent­fer­nen. Damit ist das Ergeb­nis anschließend so wie gewün­scht. Um die Abfrage in einen Tabel­len­blatt zu spe­ich­ern, Klick­en Sie entwed­er im Reg­is­ter Home auf das Sym­bol Schließen & laden oder Klick­en Sie auf den Text darunter und wählen Sie im Pull­down-Menü Schließen & laden in… Im erst­ge­nan­nten Fall wird in einem neuen Arbeits­blatt eine Tabelle mit diesem bei­den Spal­ten erstellt, wenn Sie auf den Text gek­lickt haben kön­nen Sie die Posi­tion bes­tim­men, wo die Tabelle in ein auszuwäh­len­des Arbeits­blatt gespe­ichert wer­den soll.

Die Auf­gabe ist gelöst. Und Sie haben natür­lich sofort gemerkt, dass all jene Felder, wo in der Abfrage ein Wert null einge­tra­gen war, hier in der Excel-Tabelle wirk­lich leer sind. Sie soll­ten trotz dieses Erfolges auch ein­mal die Excel-Funk­tion ZÄHLENWENN() als Ver­gle­ich ver­wen­den. Vielle­icht möcht­en Sie auch eine VBA-Rou­tine dazu schreiben. Und natür­lich kön­nen Sie auch im Forum nach­se­hen, welche Lösun­gen dort erar­beit­et wor­den sind. Und selb­stre­dend noch ein­mal der Hin­weis, dass Pow­er Query in diesem Fall seine Stärken erst dann richtig ausspielt, wenn sehr viele Dat­en vorhan­den sind oder sich die Daten­ba­sis tagtäglich ändert. 😎 

▲ nach oben …