Dubletten ab 2. Eintrag in der nebenstehenden Spalte markieren

  Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung   

In einem Forum wurde die Frage gestellt, wie eine größere Liste mit Zell-Einträgen so gekennzeichnet werden kann, dass in einer Nachbarspalte ab dem 2. Eintrag ein x eingetragen wird; der erste Wert in einer Liste soll also in der Nebenspalte leer bleiben oder einen Leerstring "" enthalten. Die ursprüngliche Reihenfolge der Daten soll im Endeffekt erhalten bleiben. Im Forum wurden unterschiedliche Varianten der ZÄHLENWENN()-Funktion vorgeschlagen, ich habe mich aber entschlossen, das ganze mit Power Query zu lösen.

Wenn Sie mein Beispiel nachvollziehen wollen, dann laden Sie idealerweise diese Datei von unserem Server herunter. Sie erkennen, dass in der einzigen Spalte 50 Text-Werte enthalten sind. Diese sollen der Vorgabe gemäß in der Nachbarspalte mit einem x versehen werden, sofern der entsprechende Wert zum zweiten, dritten, vierten … mal aufgeführt ist. Wie üblich beginnen Sie damit, die Daten in eine formatierte Tabelle umzuwandeln. Das geht recht gut, wenn eine beliebige Zelle in den Daten markiert ist und dann StrgT oder StrgL. Anschließend Daten | Aus Tabelle (in älteren Versionen Power Query | Von Tabelle) und es öffnet sich der Power Query-Editor mit den entsprechenden importierten Daten.

Im ersten Schritt werden Sie dafür sorgen, dass auf jeden Fall diese ursprüngliche, jetzt existierende Reihenfolge der Daten wiederhergestellt werden kann. Dazu wechseln Sie im Menü zum Register Spalte hinzufügen und wählen dort in der Gruppe Allgemein den Eintrag Indexspalte. Unverzüglich wird eine neue Spalte mit der Überschrift Index und einer mit 0 beginnenden fortlaufenden Nummerierung erstellt.

Der nächste Schritt ist auf den ersten Blick vielleicht nicht so ganz nachvollziehbar, aber ich versichere Ihnen, das ist ein gut funktionierender und zielführender Weg. 😉 Markieren Sie Spalte1 durch einen Rechtsklick in die Überschrift und wählen Sie im Kontextmenü Gruppieren nach … Geben Sie bei Neuer Spaltenname das Wort Data ein und wählen Sie anschließend bei Vorgang den Eintrag Alle Zeilen. Anschließend mit OK bestätigen. Das Aussehen ändert sich, denn die Spalte Index gibt es nicht mehr und dafür ist an der Stelle eine Spalte mit der Überschrift Data und jede einzelne Zelle hat den Inhalt Table:

Die neu generierte Spalte ‚Data‘

Im rechten Seitenfenster erkennen sie als letzten Eintrag Gruppierte Zeilen. Immer noch im Register Spalte hinzufügen klicken Sie in der Gruppe Allgemein auf Benutzerdefinierte Spalte. Es öffnet sich ein Dialogfenster wo sie bei Neuer Spaltenname den Text Dublette eintragen. In das große Feld Benutzerdefinierte Spaltenformel tragen Sie diese Formel ohne das führende Gleichheitszeichen ein:
= Table.AddIndexColumn([Data], "Gruppen-Index", 1, 1)
wobei sie den Spaltennamen [Data] mitsamt den eckigen Klammern durch einen Doppelklick auf den Eintrag im rechten Kasten des Fensters auswählen können:

Der Dialog mit dem Spaltennamen und der eingetragenen Formel

Nach dem schließen des Fensters ist wiederum eine neue Spalte mit der Überschrift Dublette erstellt worden und auch hier ist der Inhalt jeder Zeile Table (Tabelle). Klicken Sie in dieser Spalte in der Überschrift auf das Erweitern-Symbol Doppelpfeil (den Doppelpfeil) und es öffnet sich dieser Dialog:

Der unveränderte Erweitern-Dialog

Entfernen Sie die Häkchen bei Spalte1 und bei Ursprünglichen Spaltennamen als Präfix verwenden. Nach dem schließen dieses Fensters wird sich die Abfrage im Editor so darstellen:

Zeilen sind nun nach Spalte1 zusammengefasst

An der Spalte Index erkennen Sie, dass die ursprüngliche Reihenfolge durcheinandergebracht worden ist. Sie sehen aber auch, dass in Spalte1 die identischen Einträge zusammengefasst worden sind und in der Spalte Gruppen-Index eine fortlaufende Nummerierung geschaffen worden ist.

Erweitern Sie nun die Spalte Index durch einen Klick auf das Dreieck und sortieren Sie den Inhalt aufsteigend. Dadurch wird natürlich die gesamte Reihenfolge in die ursprüngliche Ordnung zurückversetzt. Zur Verdeutlichung habe ich in diesem Screenshot für die ersten beiden Dubletten eine Markierung eingefügt:

Die Markierung zeigt auf, dass der ‚Gruppen-Index‘ korrekt ist

Sie erkennen, dass auch die automatische Nummerierung in der Spalte Gruppen-Index dem n-ten auftreten des jeweiligen Wertes in Spalte1 entspricht. Und jetzt können Sie schnurstracks auf das Ziel zusteuern. Sie müssen „nur“ dafür sorgen, dass in einer neuen Spalte überall dort ein x eingetragen wird, wo in der Spalte Gruppen-Index ein Wert >1 enthalten ist; alle anderen Felder dieser Spalte sollen leer bleiben.

Es liegt nahe, dass sie nun eine weitere Spalte erstellen werden. Da es sich ja um eine einfache Wenn-Bedingung handelt, klicken Sie auf Bedingte Spalte. Geben Sie dort die in der folgenden Abbildung gezeigten Werte ein:

So kommen Sie zum korrekten Ergebnis

Et voilà, nach dem schließen des Fensters steht beim jeweils ersten Auftauchen des Wertes in Spalte1 der Wert null und beim zweiten, dritten, … Eintrag wie gewünscht ein x. Markieren Sie nun Spalte1, Strg und Duplikat. Rechtsklick in eine der beiden markierten Überschriften und Andere Spalten entfernen. Damit ist das Ergebnis anschließend so wie gewünscht. Um die Abfrage in einen Tabellenblatt zu speichern, klicken Sie entweder im Register Home auf das Symbol Schließen & laden oder klicken Sie auf den Text darunter und wählen Sie im Pulldown-Menü Schließen & laden in… Im erstgenannten Fall wird in einem neuen Arbeitsblatt eine Tabelle mit diesem beiden Spalten erstellt, wenn Sie auf den Text geklickt haben können Sie die Position bestimmen, wo die Tabelle in ein auszuwählendes Arbeitsblatt gespeichert werden soll.

Die Aufgabe ist gelöst. Und Sie haben natürlich sofort gemerkt, dass all jene Felder, wo in der Abfrage ein Wert null eingetragen war, hier in der Excel-Tabelle wirklich leer sind. Sie sollten trotz dieses Erfolges auch einmal die Excel-Funktion ZÄHLENWENN() als Vergleich verwenden. Vielleicht möchten Sie auch eine VBA-Routine dazu schreiben. Und natürlich können Sie auch im Forum nachsehen, welche Lösungen dort erarbeitet worden sind. Und selbstredend noch einmal der Hinweis, dass Power Query in diesem Fall seine Stärken erst dann richtig ausspielt, wenn sehr viele Daten vorhanden sind oder sich die Datenbasis tagtäglich ändert. 😎 

▲ nach oben …

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

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50  freuen …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Foren-Q&A, Power Query, Spalten bearbeiten, Text-Behandlung, Vergleichen (Daten, Tabellen, Abfragen) abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.