Familienbande(n – ID) ;-)


Video auf YouTubeHinweis:
Die Arbeitsschritte dieses Beitrages sind in einem kleinen Video dokumentiert. Das unterstützende Begleit-Video finden Sie auf YouTube an dieser Stelle.

Beachten Sie bitte: Dieses Begleit-Video enthält zusätzliche Informationen, die im folgenden Beitrag nicht dokumentiert sind!


Familienmitgliedern eine Haupt- und Sub-ID zuweisen

In einem Forum wurde die Anfrage gestellt, wie mehreren Mitgliedern einer Familie eine Haupt-und eine Unter-ID automatisch zugewiesen werden kann. Also 1-1, 1-2, 1-3 und dann beim nächsten Familiennamen 2-1, etc. Die Musterdatei erwies sich dabei als ausgesprochen suboptimal. Einerseits waren eine Unmenge an Leerzeilen quasi auf Vorrat angelegt worden, ein weitaus größeres Manko war und ist jedoch, dass es für den Nachnamen, also die eigentliche Familie keine eindeutige ID gibt. Dieser Punkt wurde seitens des Fragestellers dadurch „entkräftet“, dass eventuell vorkommende Dubletten bei Nachnamen, die jedoch nicht zur gleichen Familie gehören, von Hand so umgeändert werden, dass jede Familie einen einheitlichen Nachnamen hat. Na ja …

Erste Schritte

Zu Beginn werden sie naturgemäß diese Datei öffnen und erst einmal grob analysieren. Es handelt sich hier um die Original-Mappe aus dem Forum, darum ist sie vielleicht ohne den erklärenden Text des Fragestellers etwas verwirrend. Darum einige Kommentare von mir, damit sie das ganze etwas besser verstehen …

Das Arbeitsblatt Buchungen enthält 2 Tabellen, die mir Moment nicht wirklich etwas sagen. Wichtig ist nur, dass die Spalten G:H den endgültigen Soll-Zustand darstellen; zumindest was die IDs betrifft. Der Weg dahin führt über das Arbeitsblatt Listen. Dort sind in O1:P11 mehrere Familien und deren Mitglieder aufgeführt. In Spalte Q bzw. Spalte R ist jeweils ein mögliches Wunschergebnis für die ID aufgeführt. Ich habe mich übrigens der Einfachheit wegen für die 1. Variante entschieden, also die mit den 2 Ziffern.

Das ganze Vorhaben werde ich mit Power Query realisieren. Um der wahrscheinlichen Realität etwas näher zu kommen, kopiere ich das Blatt Listen in eine neue Mappe und entferne gleich die beiden letzten Spalten. Und der Einfachheit halber füge ich die Daten in A1 ein, aber das spielt beim weiteren Vorgehen keine Rolle. Dieser Tabelle gebe ich den Namen RawData. Mir stehen jetzt als Ausgangslage ausschließlich die relevanten Daten in einer Tabelle mit reichlich überzähligen Zeilen zur Verfügung. Und dieses File ist nun auch die Basis für die nun folgende Bearbeitung. Und ja, ich hätte durchaus diese überzähligen leeren Zeilen gar nicht mit übertragen brauchen, aber so ist das 1. der Realität näher und 2. haben Sie so Gelegenheit, einen weiteren Schritt in Power Query kennen zu lernen. 💡 

Die Lösung

Als erstes werden sie die Tabelle in den Abfrage-Editor importieren. Das geht am besten über Daten | Aus Tabelle bzw. in älteren Excel-Version über den Menüpunkt Power Query | Von Tabelle. Als Ergebnis haben sie im Editor alle Daten, einschließlich der leeren Zeilen. Es gibt allerdings zwei Dinge, die mich enorm stören: die aus meiner Sicht falschen Überschriften und die bereits angesprochenen leeren Zeilen.

Beginnen Sie damit, die Überschriften den Gegebenheiten anzupassen; klicken Sie in die Überschrift empfänger und entweder F2 oder ein Doppelklick auf den Text. Vergeben Sie die Überschrift Name. Die nächste Spalte soll die Überschrift Vorname bekommen. Im folgenden Schritt klicken Sie in der Gruppe Abfrage auf das Symbol Zeilen verringern | Zeilen entfernen | Leere Zeilen entfernen. Ruckzuck sind nur noch die relevanten Daten übriggeblieben. Diesen Zustand sollten Sie erst einmal sichern. Dazu klicken Sie im Menü Start auf den Text unterhalb des Symbols Schließen & laden | Schließen & laden in… und wählen Sie dann den Punkt Nur Verbindung erstellen. Anschließend mit Laden bestätigen.

Der Abfrage-Editor schließt sich, Sie öffnen ihn gleich wieder beispielsweise durch einen Doppelklick auf den Namen der Abfrage im rechten Seitenfenster oder Rechtsklick. Jetzt wählen Sie im Editor den Menüpunkt Spalte hinzufügen und direkt ein Klick auf Indexspalte. Dadurch wird ein null-basierter Index in einer neuen Spalte erzeugt. Idealerweise werden sie nun den schmalen Streifen links der Tabelle, den Navigationsbereich durch einen Doppelklick auf das Wort Abfrage oder das Größer-Symbol > verbreitern. Im Moment ist dort nur die einzige Abfrage mit dem Namen RawData zu sehen. Ein Rechtsklick auf diesen Namen und im Kontextmenü wählen Sie den Punkt Duplizieren. Umgehend wird eine zweite Abfrage mit dem gleichen Namen und dem „Anhängsel“ (2) erstellt und aktiviert. Zur besseren Unterscheidung gebe ich diese Abfrage schon jetzt den Namen Unikate.

Wechsel zum Menü Start. Hier ein Klick auf das Symbol Gruppieren nach und die Vorgaben in dem Dialog einfach durch OK übernehmen:

Den Dialog Gruppieren einfach so übernehmen

Den Dialog ‚Gruppieren nach‘ einfach so übernehmen

Die Abfrage stellt sich nun erheblich komprimierter dar. Die Namen sind nun wirklich Unikate, die Spalte Vornamen ist automatisch gelöscht worden. Dafür existiert dort die Spalte Anzahl, worin die berechnete Anzahl der Einträge je Name eingetragen wurde:

Automatische Berechnung der Familien-Mitglieder

Automatische Berechnung

Wählen Sie nun das Register Spalte hinzufügen und erweitern Sie das Symbol Indexspalte durch einen Klick auf den Pfeil nach unten . Hier wählen Sie die Möglichkeit, dass der eingefügte Index mit 1 beginnt und nicht mit 0. Die neu erzeugten Spalte bekommt automatisch die Überschrift Index. Im Dialog können Sie das Feld Neuer Spaltenname so belassen, sie klicken gleich in das große Textfeld Benutzerdefinierte Spaltenformel. Hier tragen Sie in exakt dieser Groß- Kleinschreibung den Beginn einer Formel ein:

List.Numbers(1,

womit sie festlegen, dass eine neue Liste -beginnend mit 1– erzeugt wird. Als 2. Argument dieser Funktion wird noch die Anzahl der Elemente benötigt. Da das ja bei Müller 3, bei Hauser 2, … sind, verwenden Sie die berechneten Ergebnisse aus der Spalte Anzahl. Einen Doppelklick im Fenster Verfügbare Spalten auf den Feldnamen Anzahl. Danach die schließende Runde Klammer, das sieht dann so aus:

List.Numbers(1,[Anzahl])

und noch ein Klick auf OK. In der (neuen) Spalte Benutzerdefiniert steht nun jeweils der Eintrag List. Klicken Sie nun auf den Doppelpfeil Doppelpfeil in der Überschrift, um diese zu erweitern. Sie haben hier 2 Möglichkeiten, nutzen Sie den oberen Punkt Auf neue Zeilen ausweiten. Sofort werden die Nachnamen auf die erforderliche Anzahl vervielfacht und die Spalte Benutzerdefiniert erhält die fortlaufende Nummerierung, beginnend mit 1 und der Schrittweite 1:

Die wieder aufgefüllten Namen der Liste

Die wieder aufgefüllten Namen der Liste

Es bedarf nicht ausgesprochen viel Fantasie um zu erkennen, dass sich hier auf 2 Spalten verteilt genau die Nummerierung steht, wie sie als ID gefordert ist; nur der Bindestrich fehlt noch. Darum markieren Sie zuerst die Überschrift Index, Shift und dann ein Klick in Benutzerdefiniert; die Reihenfolge bitte beachten! Jetzt aktivieren Sie das Menü-Register Transformieren und ein Klick auf Spalten zusammenführen. Bei Trennzeichen erweitern Sie das Dropdown, –Benutzerdefiniert– und tragen Sie dort in das darunter neu erscheinende Feld ein Minuszeichen ein. Als Neuer Spaltenname vergeben sie vorzugsweise ID. Fertig, also OK.

Weiter geht’s mit einem Klick auf das Menü Spalte hinzufügen. Wie schon vorher einmal gemacht ein Klick auf Indexspalte, um wiederum einen 0-basierten Index zu erzeugen. Damit haben die Zeilen in beiden Abfragen die gleiche Index-Nummer. Klicken Sie im linken Seitenfenster nun auf die Abfrage RawData und danach auf die Menü-Registerkarte Start. Nun die Schaltfläche Kombinieren anklicken und erweitern Sie die Auswahl Abfragen zusammenführen; hier den 2. Punkt, nämlich Abfragen als neue Abfrage zusammenführen anklicken. – Ein Klick in das leere Kombinationsfeld und dort wählen Sie dann die 2. der beiden existierenden Abfragen, Unikate. In beiden Gruppen klicken Sie nun nicht in die Überschrift sondern auf eine beliebige Zahl in der Spalte Index, im Endeffekt sind dann beide Spalten markiert. Join-Art kann so bleiben, darum sofort ein Klick auf OK.

Erweitern Sie nun die Spalte Unikate. Deaktivieren Sie das Feld (Alle Spalten auswählen) und aktivieren Sie anschließend das Kästchen ID. Idealerweise deaktivieren Sie nun noch das Kontrollkästchen Ursprünglichen Spaltennamen als Präfix verwenden und danach OK. Löschen Sie nun die Spalte Index auf eine beliebige Art und Weise. Jetzt wiederum auf den Text Schließen & laden klicken oder im Menü Datei den Punkt Schließen & laden in… wählen. Achten Sie darauf, dass der Punkt Tabelle aktiviert ist und dann Laden. Die Tabelle wird erstellt und entspricht (hoffentlich) genau dem Wunschergebnis. Wenn Sie möchten, können Sie diese Tabelle nun an eine Wunschposition verschieben. Und das war’s auch schon. Der Fragesteller jedenfalls war mit dem Ergebnis zufrieden. 🙂

▲ nach oben …

Reference: #5030

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Foren-Q&A, Power Query, PQ-Formeln (Sprache M), Text-Behandlung abgelegt und mit , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.