In einem Forum wurde die Anfrage gestellt, wie mehreren Mit­gliedern ein­er Fam­i­lie eine Haupt-und eine Unter-ID automa­tisch zugewiesen wer­den kann. Also 1–1, 1–2, 1–3 und dann beim näch­sten Fam­i­li­en­na­men 2–1, etc. Die Mus­ter­datei erwies sich dabei als aus­ge­sprochen sub­op­ti­mal. Ein­er­seits waren eine Unmenge an Leerzeilen qua­si auf Vor­rat angelegt wor­den, ein weitaus größeres Manko war und ist jedoch, dass es für den Nach­na­men, also die eigentliche Fam­i­lie keine ein­deutige ID gibt. Dieser Punkt wurde seit­ens des Fragestellers dadurch „entkräftet”, dass eventuell vork­om­mende Dublet­ten bei Nach­na­men, die jedoch nicht zur gle­ichen Fam­i­lie gehören, von Hand so umgeän­dert wer­den, dass jede Fam­i­lie einen ein­heitlichen Nach­na­men hat. Na ja …

Erste Schritte

Zu Beginn wer­den sie naturgemäß diese Datei öff­nen und erst ein­mal grob analysieren. Es han­delt sich hier um die Orig­i­nal-Mappe aus dem Forum, darum ist sie vielle­icht ohne den erk­lären­den Text des Fragestellers etwas ver­wirrend. Darum einige Kom­mentare von mir, damit sie das ganze etwas bess­er ver­ste­hen …

Das Arbeits­blatt Buchun­gen enthält 2 Tabellen, die mir Moment nicht wirk­lich etwas sagen. Wichtig ist nur, dass die Spal­ten G:H den endgülti­gen Soll-Zus­tand darstellen; zumin­d­est was die IDs bet­rifft. Der Weg dahin führt über das Arbeits­blatt Lis­ten. Dort sind in O1:P11 mehrere Fam­i­lien und deren Mit­glieder aufge­führt. In Spalte Q bzw. Spalte R ist jew­eils ein möglich­es Wun­schergeb­nis für die ID aufge­führt. Ich habe mich übri­gens der Ein­fach­heit wegen für die 1. Vari­ante entsch­ieden, also die mit den 2 Zif­fern.

Das ganze Vorhaben werde ich mit Pow­er Query real­isieren. Um der wahrschein­lichen Real­ität etwas näher zu kom­men, kopiere ich das Blatt Lis­ten in eine neue Mappe und ent­ferne gle­ich die bei­den let­zten Spal­ten. Und der Ein­fach­heit hal­ber füge ich die Dat­en in A1 ein, aber das spielt beim weit­eren Vorge­hen keine Rolle. Dieser Tabelle gebe ich den Namen Raw­Da­ta. Mir ste­hen jet­zt als Aus­gangslage auss­chließlich die rel­e­van­ten Dat­en in ein­er Tabelle mit reich­lich überzäh­li­gen Zeilen zur Ver­fü­gung. Und dieses File ist nun auch die Basis für die nun fol­gende Bear­beitung. Und ja, ich hätte dur­chaus diese überzäh­li­gen leeren Zeilen gar nicht mit über­tra­gen brauchen, aber so ist das 1. der Real­ität näher und 2. haben Sie so Gele­gen­heit, einen weit­eren Schritt in Pow­er Query ken­nen zu ler­nen. 💡 

Die Lösung

Als erstes wer­den sie die Tabelle in den Abfrage-Edi­tor importieren. Das geht am besten über Dat­en | Aus Tabelle bzw. in älteren Excel-Ver­sion über den Menüpunkt Pow­er Query | Von Tabelle. Als Ergeb­nis haben sie im Edi­tor alle Dat­en, ein­schließlich der leeren Zeilen. Es gibt allerd­ings zwei Dinge, die mich enorm stören: die aus mein­er Sicht falschen Über­schriften und die bere­its ange­sproch­enen leeren Zeilen.

Begin­nen Sie damit, die Über­schriften den Gegeben­heit­en anzu­passen; Klick­en Sie in die Über­schrift empfänger und entwed­er F2 oder ein Dop­pelk­lick auf den Text. Vergeben Sie die Über­schrift Name. Die näch­ste Spalte soll die Über­schrift Vor­name bekom­men. Im fol­gen­den Schritt Klick­en Sie in der Gruppe Abfrage auf das Sym­bol Zeilen ver­ringern | Zeilen ent­fer­nen | Leere Zeilen ent­fer­nen. Ruck­zuck sind nur noch die rel­e­van­ten Dat­en übrigge­blieben. Diesen Zus­tand soll­ten Sie erst ein­mal sich­ern. Dazu Klick­en Sie im Menü Start auf den Text unter­halb des Sym­bols Schließen & laden | Schließen & laden in… und wählen Sie dann den Punkt Nur Verbindung erstellen. Anschließend mit Laden bestäti­gen.

Der Abfrage-Edi­tor schließt sich, Sie öff­nen ihn gle­ich wieder beispiel­sweise durch einen Dop­pelk­lick auf den Namen der Abfrage im recht­en Seit­en­fen­ster oder Recht­sklick. Jet­zt wählen Sie im Edi­tor den Menüpunkt Spalte hinzufü­gen und direkt ein Klick auf Indexs­palte. Dadurch wird ein null-basiert­er Index in ein­er neuen Spalte erzeugt. Ide­al­er­weise wer­den sie nun den schmalen Streifen links der Tabelle, den Nav­i­ga­tions­bere­ich durch einen Dop­pelk­lick auf das Wort Abfrage oder das Größer-Sym­bol > ver­bre­it­ern. Im Moment ist dort nur die einzige Abfrage mit dem Namen Raw­Da­ta zu sehen. Ein Recht­sklick auf diesen Namen und im Kon­textmenü wählen Sie den Punkt Duplizieren. Umge­hend wird eine zweite Abfrage mit dem gle­ichen Namen und dem „Anhängsel” (2) erstellt und aktiviert. Zur besseren Unter­schei­dung gebe ich diese Abfrage schon jet­zt den Namen Unikate.

Wech­sel zum Menü Start. Hier ein Klick auf das Sym­bol Grup­pieren nach und die Vor­gaben in dem Dia­log ein­fach durch OK übernehmen:

Den Dia­log ‘Grup­pieren nach’ ein­fach so übernehmen

Die Abfrage stellt sich nun erhe­blich kom­prim­iert­er dar. Die Namen sind nun wirk­lich Unikate, die Spalte Vor­na­men ist automa­tisch gelöscht wor­den. Dafür existiert dort die Spalte Anzahl, worin die berech­nete Anzahl der Ein­träge je Name einge­tra­gen wurde:

Automa­tis­che Berech­nung

Wählen Sie nun das Reg­is­ter Spalte hinzufü­gen und erweit­ern Sie das Sym­bol Indexs­palte durch einen Klick auf den Pfeil nach unten . Hier wählen Sie die Möglichkeit, dass der einge­fügte Index mit 1 begin­nt und nicht mit 0. Die neu erzeugten Spalte bekommt automa­tisch die Über­schrift Index. Im Dia­log kön­nen Sie das Feld Neuer Spal­tenname so belassen, sie Klick­en gle­ich in das große Textfeld Benutzerdefinierte Spal­tenformel. Hier tra­gen Sie in exakt dieser Groß- Klein­schrei­bung den Beginn ein­er Formel ein:

List.Numbers(1,

wom­it sie fes­tle­gen, dass eine neue Liste ‑begin­nend mit 1- erzeugt wird. Als 2. Argu­ment dieser Funk­tion wird noch die Anzahl der Ele­mente benötigt. Da das ja bei Müller 3, bei Hauser 2, … sind, ver­wen­den Sie die berech­neten Ergeb­nisse aus der Spalte Anzahl. Einen Dop­pelk­lick im Fen­ster Ver­füg­bare Spal­ten auf den Feld­na­men Anzahl. Danach die schließende Runde Klam­mer, das sieht dann so aus:

List.Numbers(1,[Anzahl])

und noch ein Klick auf OK. In der (neuen) Spalte Benutzerdefiniert ste­ht nun jew­eils der Ein­trag List. Klick­en Sie nun auf den Dop­pelpfeil in der Über­schrift, um diese zu erweit­ern. Sie haben hier 2 Möglichkeit­en, nutzen Sie den oberen Punkt Auf neue Zeilen ausweit­en. Sofort wer­den die Nach­na­men auf die erforder­liche Anzahl vervielfacht und die Spalte Benutzerdefiniert erhält die fort­laufende Num­merierung, begin­nend mit 1 und der Schrit­tweite 1:

Die wieder aufge­füll­ten Namen der Liste

Es bedarf nicht aus­ge­sprochen viel Fan­tasie um zu erken­nen, dass sich hier auf 2 Spal­ten verteilt genau die Num­merierung ste­ht, wie sie als ID gefordert ist; nur der Binde­strich fehlt noch. Darum markieren Sie zuerst die Über­schrift Index, Shift und dann ein Klick in Benutzerdefiniert; die Rei­hen­folge bitte beacht­en! Jet­zt aktivieren Sie das Menü-Reg­is­ter Trans­formieren und ein Klick auf Spal­ten zusam­men­führen. Bei Trennze­ichen erweit­ern Sie das Drop­down, –Benutzerdefiniert– und tra­gen Sie dort in das darunter neu erscheinende Feld ein Minusze­ichen - ein. Als Neuer Spal­tenname vergeben sie vorzugsweise ID. Fer­tig, also OK.

Weit­er geht’s mit einem Klick auf das Menü Spalte hinzufü­gen. Wie schon vorher ein­mal gemacht ein Klick auf Indexs­palte, um wiederum einen 0‑basierten Index zu erzeu­gen. Damit haben die Zeilen in bei­den Abfra­gen die gle­iche Index-Num­mer. Klick­en Sie im linken Seit­en­fen­ster nun auf die Abfrage Raw­Da­ta und danach auf die Menü-Reg­is­terkarte Start. Nun die Schalt­fläche Kom­binieren anklick­en und erweit­ern Sie die Auswahl Abfra­gen zusam­men­führen; hier den 2. Punkt, näm­lich Abfra­gen als neue Abfrage zusam­men­führen anklick­en. – Ein Klick in das leere Kom­bi­na­tions­feld und dort wählen Sie dann die 2. der bei­den existieren­den Abfra­gen, Unikate. In bei­den Grup­pen Klick­en Sie nun nicht in die Über­schrift son­dern auf eine beliebige Zahl in der Spalte Index, im End­ef­fekt sind dann bei­de Spal­ten markiert. Join-Art kann so bleiben, darum sofort ein Klick auf OK.

Erweit­ern Sie nun die Spalte Unikate. Deak­tivieren Sie das Feld (Alle Spal­ten auswählen) und aktivieren Sie anschließend das Kästchen ID. Ide­al­er­weise deak­tivieren Sie nun noch das Kon­trol­lkästchen Ursprünglichen Spal­tenna­men als Prä­fix ver­wen­den und danach OK. Löschen Sie nun die Spalte Index auf eine beliebige Art und Weise. Jet­zt wiederum auf den Text Schließen & laden Klick­en oder im Menü Datei den Punkt Schließen & laden in… wählen. Acht­en Sie darauf, dass der Punkt Tabelle aktiviert ist und dann Laden. Die Tabelle wird erstellt und entspricht (hof­fentlich) genau dem Wun­schergeb­nis. Wenn Sie möcht­en, kön­nen Sie diese Tabelle nun an eine Wun­sch­po­si­tion ver­schieben. Und das war’s auch schon. Der Fragesteller jeden­falls war mit dem Ergeb­nis zufrieden. 🙂

▲ nach oben …