Sonderzeichen erkennen und definieren (2)

Auf der Basis der bere­its im ersten Beitrag vorgestell­ten, per copy/paste erstell­ten Arbeitsmappe sollen zu Beginn struk­turelle Änderun­gen vorgenom­men wer­den, damit eine „vernün­ftige” Auswer­tung in Pow­er Query erfol­gen kann. Nach­dem die Dat­en in auswert­bar­er Form vor­liegen wird die zweite von zwei (von mir angedacht­en) Möglichkeit­en der Auswer­tung vorgestellt. Laden Sie erforder­lichen­falls das entsprechende File hier von unserem Serv­er herunter.

In solchen Fällen ist eine grundle­gende Analyse der Daten­struk­tur uner­lässlich. Sie wer­den fest­stellen, dass grund­sät­zlich fol­gen­des dreizeiliges Schema bzw. Inter­vall zutrifft:
Name, Vor­name (mit eventuellem Titel und Zusätzen)
Leerzeile
Partei bzw. Frak­tion

… Wobei die let­zten 3 Zeilen der Gesamtliste eine Aus­nahme darstellen, es han­delt sich um die Leg­ende für nicht mehr im Par­la­ment vertretene Abge­ord­nete; ein, zwei oder drei Sterne. – Wie auch immer, Sie wer­den diese einspaltige Auf­stel­lung naturgemäß zu Beginn in den Pow­er Query-Edi­tor importieren. Da ich aus­ge­sprochen faul bin und wo möglich ver­suche, Fehlerquellen auszuschal­ten, gehe ich so vor:

• StrgPos1, um zu A1 zu gelan­gen. (Es würde auch reichen, irgend­wo in den Daten­bere­ich der Spalte A zu Klick­en.)
• StrgEnde, um zur let­zten Dat­en-Zeile zu sprin­gen.
• StrgShiftPos1 (alle Dat­en wer­den markiert).
• StrgL oder StrgT, um die Markierung in eine «Intel­li­gente» Tabelle zu for­matieren.
• Dieser Tabelle den Namen Raw­Da­ta geben.

Nun kön­nen Sie auch alle Dat­en prob­lem­los nach PQ importieren. Und gle­ich zu Beginn muss aufgeräumt wer­den. Leerzeilen haben im Rah­men ein­er geord­neten Daten­hal­tung in ein­er Liste/Tabelle nichts zu suchen. Darum ent­fer­nen Sie als erstes die Leerzeilen. Und die let­zten 3 Zeilen, welche die Leg­ende darstellen, passen auch so gar nicht in das Schema. Für diese spezielle Auf­gabe ist die Auswer­tung dieser Sta­tus nicht erforder­lich, darum soll­ten auch diese Zeilen aus der Query ent­fer­nt wer­den. Ich ver­lasse mich nicht darauf, dass immer exakt 3 Merk­male in der Leg­ende sind, darum nutze ich zum Fil­tern das Argu­ment, dass der Ein­trag in der jew­eili­gen Spalte nicht mit einem Aster­isk begin­nt.

Für den kom­menden Schritt, die Dat­en in ein­er 2‑spaltigen Tabelle (Name, Vor­name | Fraktion/Partei) anzuord­nen, gibt is in Pow­er Query diverse Wege. In diesem Beitrag stelle ich Ihnen eine eher klas­sis­che Vorge­hensweise vor, im Fol­ge­beitrag wird dann ein deut­lich ander­er, eher für „ges­tandene” User gedachte Weg ange­sprochen.

Über Spalte hinzufü­gen | Index erzeu­gen Sie eine neue Spalte mit einem 0‑basierten Index. Und sofort noch eine weit­ere Spalte mit einem 1‑basierten Index gener­ieren. Nun Start | Kom­binieren | Abfra­gen zusam­men­führen und als zu ver­Link­ende Abfrage die einzige und somit auch aktuelle Query hinzufü­gen. Im oberen Kas­ten markieren Sie die Spalte Index.1, im unteren die Spalte Index. Nach einem Klick auf OK erweit­ern Sie die neu erstellte Spalte und lassen Sie auss­chließlich bei Spalte1 das Häkchen ste­hen. PQ benen­nt diese neue Spalte automa­tisch um zu Spalte1.1. Und hir wird passend zum Wert in Spalte1 der Wert aus der näch­sten Zeile (eben­falls Spalte1) dargestellt.

Aus Grün­den der min­i­mal­is­tis­chen Daten­hal­tung löschen Sie die Spalte Index.1. In den Zeilen mit ein­er unger­aden Num­mer (1, 3, 5, …) ste­ht nun in Spalte1 der Name und in der let­zten Spalte die Frak­tion 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 Stan­dard | Mod­u­lo und tra­gen im Dia­log den Wert 2 ein. Fil­tern Sie nun Mod­u­lo so, dass nur noch der Wert 0 erhal­ten bleibt. Hin­weis: Auch wenn Sie die Spalte Index über Trans­formieren | Stan­dard | Mod­u­lo direkt zu 0 und 1 umwan­deln kön­nten halte ich den Weg über eine neue Spalte für bess­er; vielle­icht kann ich (oder kön­nen Sie) den Index ja noch für andere Zwecke ver­wen­den… 💡

Für die weit­ere Zielset­zung brauchen Sie prinzip­iell nur noch Spalte1, eventuell auch noch Spalte1.1 (die Frak­tion); löschen Sie nun alle nicht mehr benötigten Spal­ten. Bei ein­er anderen Zielset­zung würde ich an dieser Stelle Name und Vor­name in 2 Spal­ten split­ten, hier ist es aber ein Vorteil, dass bei­de Werte in 1 Spalte zusam­menge­fasst sind, denn so muss auch nur 1 Spalte (statt 2) aus­gew­ertet wer­den. Ändern Sie nun noch die Über­schrift Spalte1 zu Namen und falls die Spalte Spalte1.1 noch existiert, benen­nen Sie diese zu Frak­tio­nen um.

Prinzip­iell kön­nten Sie nun die gle­iche Vorge­hensweise wie im ersten Teil anwen­den, um die nicht deutschen Son­derze­ichen zu extrahieren. Ich zeige Ihnen hier für die gle­iche Zielset­zung einen alter­na­tiv­en Weg auf, welch­er sich bei der Fes­tle­gung der Pos­i­tiv-Zeichen (also des deutschen Alpha­bets und nicht zu erset­zende Son­derze­ichen) unter­schei­det. Und nun ist es an der Zeit, diese Abfrage per Schließen & laden in… | Nur Verbindung erstellen zu spe­ich­ern.

Sonderzeichen definieren

Das endgültige Ziel ist ja eine Liste all jen­er Son­derze­ichen zu definieren, welche in den Namen enthal­ten sind und nicht zum typ­is­chen deutschen Alpha­bet gehören. Wie schon im ersten Teil dieser Trilo­gie dargestellt, wer­den Sie als Hil­f­skon­strukt eine Pos­i­tiv-Liste jen­er Zeichen erstellen, die nicht als zu ändernde Son­derze­ichen gew­ertet wer­den sollen.

Im gle­ichen oder einem neuen Tabel­len­blatt erstellen Sie zu diesem Zweck eine Liste, die alle Zeichen des deutschen Alpha­bets in Groß- und Klein­schrei­bung umfasst, natür­lich auch die Umlaute, das ß und auch jene (wirk­lichen) Son­derze­ichen, die in den Tex­ten vorkom­men kön­nen aber nicht in die Kor­rek­tur-Liste aufgenom­men wer­den sollen.

Angenom­men, Sie schreiben diese Liste in ein neues Tabel­len­blatt, dann Klick­en Sie in A1 und tra­gen diese Funk­tion ein: =ZEICHEN(ZEILE(A65)). Das Funk­tion­sergeb­nis wird A sein. Ziehen Sie diese Zelle mit der Maus nun soweit nach unten, bis das Z erre­icht ist. Naturgemäß ist dieses Zeile 26, denn das „nor­male“ Alpha­bet hat nun ein­mal 26 Zeichen. In die Zelle A27 tra­gen Sie die Funk­tion =ZEICHEN(ZEILE(A97)) ein und ziehen dann auch diese Zelle bis zur Zeile 52 nach unten. Geben Sie nun nacheinan­der in beliebiger Rei­hen­folge ganz nor­mal per Tas­tatur die Umlaute in bei­den Schreib­weisen, das ß und jene Son­derze­ichen ein, die nicht in der Erset­zen-Liste aufge­führt wer­den sollen. Wenn Sie an das eine oder andere Zeichen nicht gedacht haben, lässt sich das später prob­lem­los ergänzen. 😉

Über beispiel­sweise StrgT oder StrgL wan­deln Sie diese Auf­stel­lung in eine for­matierte Liste um. Ide­al­er­weise geben Sie dieser Liste auch gle­ich einen „sprechen­den“ Namen, beispiel­sweise DE_Zeichen und als Über­schrift gle­ich Zeichen ein­tra­gen bzw. ändern. Hin­weis: Wenn ich weiß, dass auch „exper­i­men­tier­freudi­ge“ Mitar­beit­er mit diesen Dat­en arbeit­en, dann markiere ich die gesamte Liste und über Kopieren | Als Wert ein­fü­gen sorge ich dafür, dass die Werte auch beim Ver­schieben der Liste unverän­dert bleiben.

Nun importierten Sie auch diese for­matierte Tabelle in den Pow­er Query-Edi­tor. Und da es sich ja nur um eine Hil­f­sta­belle han­delt, soll­ten Sie diese gle­ich per Schließen & laden in… | Nur Verbindung erstellen sich­ern.

Der nun fol­gende Schritt ist ‑wie auch im ersten Beitrag dargestellt- der Ein­stieg zum eigentlichen Ziel, dem Erken­nen und separi­eren der zu erset­zen­den Son­derze­ichen, geht aber einen kom­plett anderen Weg. – Öff­nen Sie die Abfrage Raw­Da­ta. Markieren Sie zu Beginn die Spalte Namen und fügen Sie eine neue, benutzerdefinierte Spalte ein.  Da Sie jet­zt jede Zelle so split­ten wer­den, dass jedes Zeichen in ein­er eige­nen Zelle/Zeile ste­ht, soll­ten Sie Zeichen als Über­schrift ver­wen­den. Zur Erre­ichung dieses Ziels geben Sie diese Funk­tion (Formel) ein:
= Text.ToList([Namen])

Jedes Zeichen jed­er Zelle wird in eine eigene Zeile geschrieben, wenn Sie die neu gener­ierte Spalte Zeichen erweit­ern Auf neue Zeilen ausweit­en wählen. Nun über Start | Kom­binieren | Abfra­gen zusam­men­führen als zweite Abfrage (Sekundär-Abfrage, im unteren Kas­ten) die Query DE_Zeichen wählen. Wenn Sie in der Primär- und Sekundär-Abfrage jew­eils die Spalte Zeichen markieren, kön­nen Sie das Ganze per OK bestäti­gen, denn die Vor­gabe der Join-Art ist okay.

Als Ergeb­nis ste­ht nach dem Klick auf    in jed­er Zelle der neu erstell­ten Spalte (jet­zt Zeichen.1) entwed­er das gle­iche Zeichen wie in Zeichen oder schein­bar gar nichts oder vielle­icht auch null (vorzugsweise, wenn Sie ein zu behal­tendes Son­derze­ichen in der Liste „vergessen” hat­ten).

Wenn Sie sich verdeut­lichen, was das Ergeb­nis der Spalte1.1 bedeutet wer­den Sie fest­stellen, dass bei ein­er Übere­in­stim­mung mit Spalte Zeichen gewährleis­tet ist, dass dieses Zeichen kein zu ändern­des Son­derze­ichen ist; diese Zeilen kön­nen also gelöscht wer­den. Dazu bietet es sich an, eine Benutzerdefinierte Spalte mit der Formel
= [Zeichen]<>[Zeichen.1]
zu erstellen. Fil­tern Sie diese Spalte nach TRUE und es verbleiben auss­chließlich jene Zeilen, wo Zeichen enthal­ten sind, welche nicht in der händisch erstell­ten Liste aufge­führt sind. „Just for test­ing” hat­te ich in mein­er Liste DE_Zeichen ein­mal das Kom­ma, welch­es ja Namen und Vor­na­men tren­nt „vergessen” und da hat­te ich natür­lich entsch­ieden zu viele Zeilen. Den PQ-Edi­tor schließen, Änderun­gen natür­lich beibehal­ten und die for­matierte Liste  Zeichen  um eine Zeile mit dem , erweit­ert. Die Abfrage Namen erneut öff­nen und eventuell Vorschau aktu­al­isieren, falls es Ihre Grun­de­in­stel­lun­gen so ver­lan­gen. Nach­dem Sie die Dup­likate in der Spalte Zeichen ent­fer­nt haben, kön­nen Sie das Ergeb­nis nach Ihren Erfordernissen nutzen.

▲ nach oben …