Sonderzeichen erkennen und definieren (2)

Xtract: Sonderzeichen außerhalb des lateinischen, deutschen Alphabets erkennen und für spätere Verwendung separieren. Teil 2: Eine sehr gewöhnungsbedürftige „unsaubere“ Datenbasis und eine andere Methode der Separierung.

Wissensstand: Level 3 ⇒ Excel GUT!, mindestens Basis-Kenntnisse in Power Query

Auf der Basis der bereits im ersten Beitrag vorgestellten, per copy/paste erstellten Arbeitsmappe sollen zu Beginn strukturelle Änderungen vorgenommen werden, damit eine „vernünftige“ Auswertung in Power Query erfolgen kann. Nachdem die Daten in auswertbarer Form vorliegen wird die zweite von zwei (von mir angedachten) Möglichkeiten der Auswertung vorgestellt. Laden Sie erforderlichenfalls das entsprechende File hier von unserem Server herunter.

In solchen Fällen ist eine grundlegende Analyse der Datenstruktur unerlässlich. Sie werden feststellen, dass grundsätzlich folgendes dreizeiliges Schema bzw. Intervall zutrifft:
Name, Vorname (mit eventuellem Titel und Zusätzen)
Leerzeile
Partei bzw. Fraktion

… Wobei die letzten 3 Zeilen der Gesamtliste eine Ausnahme darstellen, es handelt sich um die Legende für nicht mehr im Parlament vertretene Abgeordnete; ein, zwei oder drei Sterne. – Wie auch immer, Sie werden diese einspaltige Aufstellung naturgemäß zu Beginn in den Power Query-Editor importieren. Da ich ausgesprochen faul bin und wo möglich versuche, Fehlerquellen auszuschalten, gehe ich so vor:

  • StrgPos1, um zu A1 zu gelangen. (Es würde auch reichen, irgendwo in den Datenbereich der Spalte A zu klicken.)
  • StrgEnde, um zur letzten Daten-Zeile zu springen.
  • StrgShiftPos1 (alle Daten werden markiert).
  • StrgL oder StrgT, um die Markierung in eine «Intelligente» Tabelle zu formatieren.
  • Dieser Tabelle den Namen RawData geben.

Nun können Sie auch alle Daten problemlos nach PQ importieren. Und gleich zu Beginn muss aufgeräumt werden. Leerzeilen haben im Rahmen einer geordneten Datenhaltung in einer Liste/Tabelle nichts zu suchen. Darum entfernen Sie als erstes die Leerzeilen. Und die letzten 3 Zeilen, welche die Legende darstellen, passen auch so gar nicht in das Schema. Für diese spezielle Aufgabe ist die Auswertung dieser Status nicht erforderlich, darum sollten auch diese Zeilen aus der Query entfernt werden. Ich verlasse mich nicht darauf, dass immer exakt 3 Merkmale in der Legende sind, darum nutze ich zum Filtern das Argument, dass der Eintrag in der jeweiligen Spalte nicht mit einem Asterisk beginnt.

Für den kommenden Schritt, die Daten in einer 2-spaltigen Tabelle (Name, Vorname | Fraktion/Partei) anzuordnen, gibt is in Power Query diverse Wege. In diesem Beitrag stelle ich Ihnen eine eher klassische Vorgehensweise vor, im Folgebeitrag wird dann ein deutlich anderer, eher für „gestandene“ User gedachte Weg angesprochen.


Über Spalte hinzufügen | Index erzeugen Sie eine neue Spalte mit einem 0-basierten Index. Und sofort noch eine weitere Spalte mit einem 1-basierten Index generieren. Nun Start | Kombinieren | Abfragen zusammenführen und als zu verlinkende Abfrage die einzige und somit auch aktuelle Query hinzufügen. Im oberen Kasten markieren Sie die Spalte Index.1, im unteren die Spalte Index. Nach einem Klick auf OK erweitern Sie die neu erstellte Spalte und lassen Sie ausschließlich bei Spalte1 das Häkchen stehen. PQ benennt diese neue Spalte automatisch um zu Spalte1.1. Und hir wird passend zum Wert in Spalte1 der Wert aus der nächsten Zeile (ebenfalls Spalte1) dargestellt.

Aus Gründen der minimalistischen Datenhaltung löschen Sie die Spalte Index.1. In den Zeilen mit einer ungeraden Nummer (1, 3, 5, …) steht nun in Spalte1 der Name und in der letzten Spalte die Fraktion bzw. Partei. Um nun jede zweite Zeile zu löschen markieren Sie die Spalte Index, fügen eine neue Spalte hinzu, in der Gruppe Aus Zahl wählen Sie Standard | Modulo und tragen im Dialog den Wert 2 ein. Filtern Sie nun Modulo so, dass nur noch der Wert 0 erhalten bleibt. Hinweis: Auch wenn Sie die Spalte Index über Transformieren | Standard | Modulo direkt zu 0 und 1 umwandeln könnten halte ich den Weg über eine neue Spalte für besser; vielleicht kann ich (oder können Sie) den Index ja noch für andere Zwecke verwenden… 💡

Für die weitere Zielsetzung brauchen Sie prinzipiell nur noch Spalte1, eventuell auch noch Spalte1.1 (die Fraktion); löschen Sie nun alle nicht mehr benötigten Spalten. Bei einer anderen Zielsetzung würde ich an dieser Stelle Name und Vorname in 2 Spalten splitten, hier ist es aber ein Vorteil, dass beide Werte in 1 Spalte zusammengefasst sind, denn so muss auch nur 1 Spalte (statt 2) ausgewertet werden. Ändern Sie nun noch die Überschrift Spalte1 zu Namen und falls die Spalte Spalte1.1 noch existiert, benennen Sie diese zu Fraktionen um.

Prinzipiell könnten Sie nun die gleiche Vorgehensweise wie im ersten Teil anwenden, um die nicht deutschen Sonderzeichen zu extrahieren. Ich zeige Ihnen hier für die gleiche Zielsetzung einen alternativen Weg auf, welcher sich bei der Festlegung der Positiv-Zeichen (also des deutschen Alphabets und nicht zu ersetzende Sonderzeichen) unterscheidet. Und nun ist es an der Zeit, diese Abfrage per Schließen & laden in… | Nur Verbindung erstellen zu speichern.

Sonderzeichen definieren

Das endgültige Ziel ist ja eine Liste all jener Sonderzeichen zu definieren, welche in den Namen enthalten sind und nicht zum typischen deutschen Alphabet gehören. Wie schon im ersten Teil dieser Trilogie dargestellt, werden Sie als Hilfskonstrukt eine Positiv-Liste jener Zeichen erstellen, die nicht als zu ändernde Sonderzeichen gewertet werden sollen.

Im gleichen oder einem neuen Tabellenblatt erstellen Sie zu diesem Zweck eine Liste, die alle Zeichen des deutschen Alphabets in Groß- und Kleinschreibung umfasst, natürlich auch die Umlaute, das ß und auch jene (wirklichen) Sonderzeichen, die in den Texten vorkommen können aber nicht in die Korrektur-Liste aufgenommen werden sollen.

Angenommen, Sie schreiben diese Liste in ein neues Tabellenblatt, dann klicken Sie in A1 und tragen diese Funktion ein: =ZEICHEN(ZEILE(A65)). Das Funktionsergebnis wird A sein. Ziehen Sie diese Zelle mit der Maus nun soweit nach unten, bis das Z erreicht ist. Naturgemäß ist dieses Zeile 26, denn das „normale“ Alphabet hat nun einmal 26 Zeichen. In die Zelle A27 tragen Sie die Funktion =ZEICHEN(ZEILE(A97)) ein und ziehen dann auch diese Zelle bis zur Zeile 52 nach unten. Geben Sie nun nacheinander in beliebiger Reihenfolge ganz normal per Tastatur die Umlaute in beiden Schreibweisen, das ß und jene Sonderzeichen ein, die nicht in der Ersetzen-Liste aufgeführt werden sollen. Wenn Sie an das eine oder andere Zeichen nicht gedacht haben, lässt sich das später problemlos ergänzen. 😉

Über beispielsweise StrgT oder StrgL wandeln Sie diese Aufstellung in eine formatierte Liste um. Idealerweise geben Sie dieser Liste auch gleich einen „sprechenden“ Namen, beispielsweise DE_Zeichen und als Überschrift gleich Zeichen eintragen bzw. ändern. Hinweis: Wenn ich weiß, dass auch „experimentierfreudige“ Mitarbeiter mit diesen Daten arbeiten, dann markiere ich die gesamte Liste und über Kopieren | Als Wert einfügen sorge ich dafür, dass die Werte auch beim Verschieben der Liste unverändert bleiben.

Nun importierten Sie auch diese formatierte Tabelle in den Power Query-Editor. Und da es sich ja nur um eine Hilfstabelle handelt, sollten Sie diese gleich per Schließen & laden in… | Nur Verbindung erstellen sichern.


Der nun folgende Schritt ist -wie auch im ersten Beitrag dargestellt- der Einstieg zum eigentlichen Ziel, dem Erkennen und separieren der zu ersetzenden Sonderzeichen, geht aber einen komplett anderen Weg. – Öffnen Sie die Abfrage RawData. Markieren Sie zu Beginn die Spalte Namen und fügen Sie eine neue, benutzerdefinierte Spalte ein.  Da Sie jetzt jede Zelle so splitten werden, dass jedes Zeichen in einer eigenen Zelle/Zeile steht, sollten Sie Zeichen als Überschrift verwenden. Zur Erreichung dieses Ziels geben Sie diese Funktion (Formel) ein:
= Text.ToList([Namen])

Jedes Zeichen jeder Zelle wird in eine eigene Zeile geschrieben, wenn Sie die neu generierte Spalte Zeichen erweitern Auf neue Zeilen ausweiten wählen. Nun über Start | Kombinieren | Abfragen zusammenführen als zweite Abfrage (Sekundär-Abfrage, im unteren Kasten) die Query DE_Zeichen wählen. Wenn Sie in der Primär- und Sekundär-Abfrage jeweils die Spalte Zeichen markieren, können Sie das Ganze per OK bestätigen, denn die Vorgabe der Join-Art ist okay.

Als Ergebnis steht nach dem Klick auf  Doppelpfeil  in jeder Zelle der neu erstellten Spalte (jetzt Zeichen.1) entweder das gleiche Zeichen wie in Zeichen oder scheinbar gar nichts oder vielleicht auch null (vorzugsweise, wenn Sie ein zu behaltendes Sonderzeichen in der Liste „vergessen“ hatten).

Wenn Sie sich verdeutlichen, was das Ergebnis der Spalte1.1 bedeutet werden Sie feststellen, dass bei einer Übereinstimmung mit Spalte Zeichen gewährleistet ist, dass dieses Zeichen kein zu änderndes Sonderzeichen ist; diese Zeilen können also gelöscht werden. Dazu bietet es sich an, eine Benutzerdefinierte Spalte mit der Formel
= [Zeichen]<>[Zeichen.1]
zu erstellen. Filtern Sie diese Spalte nach TRUE und es verbleiben ausschließlich jene Zeilen, wo Zeichen enthalten sind, welche nicht in der händisch erstellten Liste aufgeführt sind. „Just for testing“ hatte ich in meiner Liste DE_Zeichen einmal das Komma, welches ja Namen und Vornamen trennt „vergessen“ und da hatte ich natürlich entschieden zu viele Zeilen. Den PQ-Editor schließen, Änderungen natürlich beibehalten und die formatierte Liste  Zeichen  um eine Zeile mit dem , erweitert. Die Abfrage Namen erneut öffnen und eventuell Vorschau aktualisieren, falls es Ihre Grundeinstellungen so verlangen. Nachdem Sie die Duplikate in der Spalte Zeichen entfernt haben, können Sie das Ergebnis nach Ihren Erfordernissen nutzen.

▲ nach oben …

Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

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

Dann würde ich mich über einen Beitrag Ihrerseits z.B.  € 2,50 freuen … (← Klick mich!)

Dieser Beitrag wurde unter Filtern & Sortieren, Foren-Q&A, Power Query, PQ-Formeln (Sprache M), Spalten bearbeiten, Text-Behandlung, {Liste} abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.