Zahlen und zählen
Zu Beginn angemerkt: Jeder der hier 5 gestellten Aufgaben lässt sich auch mit Formeln problemlos lösen. Aber insbesondere bei größeren Dateien mit vielen unterschiedlichen Kunden-Namen artet das manchmal richtig in Arbeit aus. Ich habe Ihnen eine Liste mit 40 Einträgen vorbereitet, die sie bitte hier herunterladen. Das Ganze stellt sich in etwa so dar:
Die Basis-Daten für die Übungen
Aufgabe 1: Wie viele verschiedene Namen sind in der Liste enthalten?
Aufgabe 2: Wie viele (unterschiedliche) Kunden, Interessenten, Lieferanten sind dort aufgeführt?
Aufgabe 3: Wie oft erscheint jeder Name in der Liste?
Aufgabe 4: Wie viele Kunden haben weniger als 1.000 € Umsatz gemacht?
Aufgabe 5: Wie viele Kunden und wie viele Lieferanten haben jeweils im 1. und 2. Halbjahr 2016 Umsatz getätigt?
Grundsätzlich gilt, dass sie für jede dieser Aufgaben ein Duplikat der Basisdaten (eigentlich der Basis-Abfrage) erstellen. Damit ist gemeint, dass sie zu Beginn ein Mal die Basisdaten in den Abfrage-Editor laden. Anschließend für jede Lösung im Menü Start, Gruppe: Abfrage Auswahl: Verwalten den Punkt Duplizieren wählen und dann mit der jeweiligen Kopie arbeiten. Zweckmäßigerweise werden sie jeder dieser erstellten Abfragen einen sinnvollen „sprechenden” Namen geben. Stören Sie sich bitte nicht daran, dass ich als Arbeitstitel Lösung # verwende, Sie sollten andere Namen verwenden. – Ich selbst habe der Optik wegen der 3. Spalte (Letztes Datum) gleich in der ersten Abfrage den Datentyp Datum zugewiesen und erst danach erst die Duplikate erstellt. So ist gewährleistet, dass diese „schlanke” Darstellung durchgängig ist und nicht in jeder Lösung einzeln nachgebessert werden muss.
Aufgabe 1: Lösung
Wie viele verschiedene Namen sind in der Liste enthalten?
In dem erstellten Duplikat ein Klick auf die Überschrift Name, Menü Start, Gruppe Abfrage, Symbol Zeilen verringern | Zeilen entfernen | Duplikate entfernen. Das Ganze stellt sich vor dem Klick so dar:
Der Menü-Baum zum entfernen von Duplikaten in markieren Spalten
Nach dem Entfernen der doppelten Einträge der ersten Spalte hat sich die Anzahl der Zeilen stark verringert:
Sehr viel weniger Zeilen nach Entfernung der doppelten Namen
Nun ein Wechsel zum Menü Transformieren und dort in der Gruppe Tabelle ein Klick auf Zeilen zählen. Das Ergebnis ist auf den ersten Blick vielleicht etwas irritierend:
Dieses Ergebnis ist gewiss unerwartet…
Aber mit einem Klick auf das erste Symbol: Zu Tabelle erreichen Sie, dass das Ergebnis in eine neue Tabelle geschrieben wird:
Das Ergebnis ist ansehnlicher und stimmt nun 😎
Es bietet sich noch an, die Überschrift dem Inhalt der einzigen Zeile anzupassen. Ich verwende beispielsweise Anzahl Namen. – Schließen & laden und die Aufgabe ist erledigt. Wenn Sie jedoch gleich weitermachen wollen, brauchen Sie diese Abfrage nicht zu schließen sondern aktivieren wiederum die Abfrage mit den Basis-Daten (idealerweise im linken Seitenfenster) und erstellen für die kommende Aufgabe erneut ein Duplikat.
Hinweis: Ich weiß, dass ich hier etwas „geschludert” habe. Für den Einstig sollte das aber ausreichend sein. Bei der nächsten Lösung werden Sie erkennen, was ich meine…
Aufgabe 2: Lösung
Wie viele (unterschiedliche) Kunden, Interessenten, Lieferanten sind dort aufgeführt?
Die Aufgabe ähnelt der ersten sehr stark, allerdings sollen hier die Merkmale differenziert und anschließend ausgezählt werden. Dennoch ist das Vorgehen in Teilen ganz anders als bei der ersten Lösung. Markieren Sie in der duplizierten Abfrage die Spalte Merkmal durch einen Klick in die Überschrift, dann Gruppe Transformieren | Gruppieren nach und es zeigt sich dieses Fenster:
Hier werden die Weichen für die Gruppierung gestellt
Der Klarheit wegen bietet es sich an, das Textfeld Neuer Spaltenname mit Anzahl nach Merkmal auszufüllen. Dann einfach ein Klick auf die Schaltfläche OK. Das Ergebnis ist rasch da und sollte Sie überzeugen oder vielleicht auch irritieren:
Die Anzahl aller Zeilen mit Kunden, Interessenten und Lieferanten
Prinzipiell ist diese Berechnung korrekt. Aber eben nur prinzipiell, denn eigentlich sollte oder dürfte die Summe der Spalte Anzahl nur so hoch sein wie die Summe der von Duplikaten befreiten Namen. Hier wurden aber mehrfach aufgeführte Namen auch mehrfach gezählt. 😕
Wenn Sie also berechnen wollen, wie viele unterschiedliche Personen/Firmen jeweils vorhanden sind, müssen Sie zuerst die Namen von Duplikaten befreien und dann den Weg der Gruppierung gehen. Ihr Ergebnis wird dann vermutlich so sein:
Auch falsch…
Sie merken es schon an der Bild-Beschriftung, auch hier stimmt etwas nicht. Aber warum nicht? Sie wissen aus der ersten Aufgabe, dass die bereinigte Zahl der Namen 7 ist. Und dieses Ergebnis hat als Summe der Spalte Anzahl auch 7. Ja, aber Namen haben nun einmal die Eigenschaft, dass ein gleicher Name nicht unbedingt heißen muss, dass das die gleiche Person ist. Wenn Sie sich die Basis-Daten ansehen werden Sie erkennen, dass beispielsweise der nicht gerade seltene Name Müller in allen drei Kategorien erscheint. Andere Namen sind in zwei Merkmalen vertreten. Und da keine eindeutige ID existiert muss davon ausgegangen werden, dass nur die Kombination von Name und Merkmal eine Eindeutigkeit gewährleistet. Darum erstellen Sie wiederum ein Duplikat der ersten Abfrage, markieren Sie die Spalten Name und Merkmal und dann entfernen Sie die Duplikate:
Die korrekte von Duplikaten bereinigte Liste (Abfrage)
Sie sehen, dass es nun 16 Datensätze sind, was auch der Realität entspricht. Sie erkennen, dass eine eindeutige ID (Kunden-/Interessentennummer, Lieferantennummer) das Unterfangen etwas erleichtert hätte. Insbesondere wenn ein Interessent zum Kunden wird und zwar den Status ändert, die ID aber behält. Oder der Name ändert sich durch Heirat, Umfirmierung; auch hier bleibt natürlich die ID erhalten und führt zu mehr Eindeutigkeit. Wie dem auch sei, die Datenlage ist hier anders. – Wenn Sie unter diesem Aspekt auf genau diesem Stand der Dinge die Spalte Merkmal markieren und dann die Gruppierung durchführen kommen Sie auf das gewünschte Ergebnis:
Jetzt stimmt’s endlich 😉
Aufgabe 3: Lösung
Wie oft erscheint jeder Name in der Liste?
Diese Aufgabe sollte jetzt eigentlich ein „Selbstgänger” sein. Sie erstellen wiederum ein Duplikat aus den Basis-Daten, markieren erforderlichenfalls die erste Spalte und Klicken dann auf die Schaltfläche Gruppieren. Auf Wunsch können Sie dann natürlich noch die Überschrift der zweiten Spalte anpassen. Und vergessen Sie nicht, die Abfrage mit einem „sprechenden” Namen zu versehen.
Aber bedenken Sie, dass hier nur der Name ohne das dazugehörige Merkmal betrachtet wurde. Wahrscheinlich wollen Sie (und vor allen Dingen ich 😎 ) ein anderes Ergebnis: Wie oft erschein jede Namens-Kombination in der Liste? Verwenden Sie dazu die Basis-Abfrage (natürlich als Kopie), markieren Sie Name und Merkmal und dann Gruppieren nach. Vergeben Sie einen sinnvollen Namen und das Ergebnis stimmt:
Wer erscheint wie oft in der Liste?
Intermezzo
Dieser Zwischenschritt ist ziemlich anspruchsvoll. Wenn Sie sich ausschließlich mit den Basics auseinandersetzen wollen, dann überspringen Sie den Part und machen gleich hier weiter. In dem Fall werden Sie aber statt der in der direkt hiernach folgenden Anleitung angegebenen ID immer die beiden ersten Spalten (Name und Merkmal) markieren müssen. Das Vorgehen ist ja auch schon mehrfach beschrieben.
Ehrlich gesagt, so ganz allmählich „nervt” mich die andauernde Notwendigkeit, immer die Kombination Name | Merkmal zu betrachten und zu markieren, weil ja (nur) der Name nicht eindeutig ist. Um das zu ändern, mache ich einen kleinen Abstecher: Duplikat der ersten Abfrage erstellen, hier (letztmalig) die ersten beiden Spalten markieren und die Duplikate entfernen, wie es auch schon weiter oben beschrieben wurde. Dann Register Spalte hinzufügen, DropDown Indexspalte 
| Benutzerdefiniert… | Startindex: 1000 | Schrittweite: 5. Nach einem OK wird jede eindeutige Namens-Kombination auch eine eindeutige Nummer haben. Ich verschiebe nun diese Index-Spalte an die erste Position, weil ich es gewohnt bin, dass IDs zuerst aufgeführt werden; das muss aber nicht unbedingt sein 😉 . Geben Sie der Abfrage beispielsweise den Namen mit ID, um sie gleich besser wieder zu finden. Nun noch Schließen & laden (Menü Start oder Datei).
Wechseln Sie in die Abfrage der Basis-Daten und erstellen Sie erneut eine Kopie per Duplizieren. Belassen Sie es gerne bei dem vorgegebenen Namen Tabelle1 (2), den erkennen Sie dann in der Abbildung hierunter wieder. 😉 Register Start, Schaltfläche Kombinieren | Abfragen zusammenführen:
Der erste Schritt zum Zusammenführen von Abfragen
In das leere Kombinationsfeld unterhalb der ersten Auflistung Klicken und den Eintrag mit ID auswählen. Sofort wird direkt darunter ein Daten-Auszug der zweiten Abfrage angezeigt. Nun müssen Sie in beiden Tabellen jene Spalten markieren, die in beiden Listen identische und eindeutige Einträge haben. Markieren Sie also jene 4 Spalten, wie hier gezeigt Shift oder Strg ist sehr hilfreich:
Die Kombination der jeweils 2 Spalten markieren
Nach einem OK kommt spätestens auf den zweiten Blick eine mehr oder weniger große Verwirrung, weil das Ergebnis wahrscheinlich nicht dem entspricht, was Sie erwartet haben:
Schön, was soll mir das in NewColumn nun sagen? 😕
Zugegeben, ich war beim ersten Anblick eines solchen Ergebnisses auch mehr als nur etwas irritiert. Und wenn Sie nicht so tief in der EDV drinstecken, dann kennen Sie die Vokabel „Table” vielleicht nur als „Tisch”; es ist aber auch eine „Tabelle”, und genau das trifft hier auch zu. Klicken Sie in der Überschrift auf dem Doppelpfeil rechts 
(das Erweitern-Symbol) und es erscheint dieser Dialog:
Welche Spalten sollen erweitert werden?
Jetzt noch 3 Mausklicks auf diese Kästchen:
- (Alle Spalten auswählen)
- Ursprünglichen Spaltennamen als Präfix verwenden
- und Index, um das Häkchen hier wieder zu setzen.
Eine Kontrolle wird Ihnen zeigen, dass jetzt identische Namen | Merkmal-Kombinationen auch eine identische ID haben. Und das ist ja auch der Sinn des Unternehmens gewesen. Und jetzt „dürfen” Sie noch einmal die Duplikate entfernen, indem Sie (nur) den Index als Auswahl für die Duplikat-Suche verwenden. Und das funktioniert natürlich auch dann noch, wenn Frau oder Herr Winter etwas kauft und damit den Status Interessent verliert und zum Kunden wird. Denn die ID bleibt erhalten.
Aufgabe 4: Lösung
Wie viele Kunden haben weniger als 1.000 € Umsatz gemacht?
Hier ist vielleicht zu Beginn etwas mehr Überlegung gefragt. Es geht hier nicht darum zu zählen, wie oft ein Umsatz oder Einkauf unter 1.000 € gemacht worden ist sondern es betrifft ausschließlich Kunden, nicht die Lieferanten; Interessenten haben ja sowieso keinen Umsatz gemacht, sonst wären es ja Kunden.
Darum werden sie im 1. Schritt beim Merkmal einen Filter auf Kunde setzen. Dann erst sollten Sie in der Spalte Umsatz in den Zahlenfilter auf <1000 setzen.
So wie sich die Abfrage zeigt, ist sie an sich schon ganz brauchbar. Und wenn die Fragestellung gewesen wäre, welche Kunden wann den geringen Umsatz gemacht haben, dann wäre diese Abfrage zielführend. Aber es ist ja „nur” die Anzahl der Kunden gefragt, auf welche dieses Merkmal zutrifft. Darum werden sie in das Menü Transformieren wechseln und dort wiederum die Auswahl Zeilen zählen anklicken. Wie schon früher werden sie auf die erste Schaltfläche Klicken, um aus dem Ergebnis eine Tabelle zu erstellen. Auch hier bietet sich an, Spaltennamen den Gegebenheiten anzupassen. Beispielsweise: Anzahl Kunden < 1000 €. Ihr Ergebnis sollte zwischen 4 und 6 liegen. 😉
Aufgabe 5: Lösung
Wie viele Kunden und wie viele Lieferanten haben jeweils im 1. und 2. Halbjahr 2016 Umsatz getätigt?
Um die knappe Fragestellung noch einmal zu verdeutlichen: Es sollen für das erste und das zweite Halbjahr getrennt festgestellt werden, wie viele unterschiedliche Kunden (also die Anzahl) in dem jeweiligen Zeitraum Umsatz gemacht haben und wie viele unterschiedliche Lieferanten uns im ersten und wieviel uns im zweiten Halbjahr eine Rechnung gesandt haben.
Zu Beginn setzen Sie bei Umsatz erst einmal einen Filter: Sie entfernen in der Auflistung das Häkchen bei (NULL), womit Sie automatisch auch die Interessenten eliminieren. Denn in dem Moment, wo ein Interessent Umsatz tätig, ist er automatisch zu einem Kunden geworden (sofern das auch entsprechend angepasst wurde).
Um das Ganze nicht zu verkomplizieren, werden sie nicht nur eine, sondern zwei Abfragen erstellen; eine für jedes Halbjahr. Im Idealfall werden sie jetzt schon den Namen für die Abfrage ändern, beispielsweise auf qry_Anzahl Ku & Li HJ_1 2016 für das erste Halbjahr. Da bis hierhin die ersten Schritte für beide Halbjahre gleich sind, sollten Sie an dieser Stelle schon von dieser Abfrage eine Kopie erstellen und diesem Duplikat den entsprechenden Namen geben.
Wechseln Sie nun wieder in die Abfrage des ersten Halbjahrs. Hier setzen Sie in der Datum-Spalte einen Filter. Die Auswahl Datumsfilter bietet Ihnen den Punkt zwischen… an und hier geben Sie in die entsprechenden Felder den 1.1.2016 und den 30.6.2016 als Eckwert ein. OK und in der Query sind nur noch die Daten des 1. Halbjahres enthalten. Wenn Sie jetzt Kunden und Lieferanten separieren wollen wird Ihnen auffallen, dass nicht ein einziger Lieferant in der Liste vertreten ist. Das ist kein Fehler, das ist gewollt so. 😉 Also gibt es hier nichts zu filtern und somit gleich Transformieren | Zeilen zählen. Geben Sie der Spalte nur noch einen sinnvollen Namen, beispielsweise Anzahl Kunden 1. HJ 16.
Hier mal eine Frage an Sie: stimmt das Ergebnis mit 10 Kunden? Na ja, wenn ich schon die Frage stelle, dann ist doch bestimmt irgendwo ein Haken an der Sache. 😎 Richtig, die berechnete Zahl ist nicht der Zielvorgabe entsprechend. Es sollen natürlich nicht die Gesamtzahl der Käufe aufgeführt werden sondern die Gesamtzahl unterschiedlicher Kunden. Klicken Sie einmal im rechten Seitenfenster auf die hier von mir markierte Zeile:
So wählen Sie einen Zwischenschritt der Abfrage aus
Und sie werden im eigentlichen Abfrage-Fenster erkennen, dass beispielsweise der Kunde Müller 3 Mal vertreten ist:
Trotz Filter ist der Kunde Müller 3 Mal vorhanden
Belassen Sie es bei dieser Markierung am rechten Seitenfenster; achten Sie darauf, dass in der Abfrage die Spalte Name markiert ist. Entfernen Sie nun die Duplikate. Power Query erkennt nun, dass die Folgeschritte zu einem anderen Ergebnis führen könnten. Darum wird Ihnen diese Abfrage eingeblendet:
Sicherheitsabfrage vor dem einfügen eines Schrittes
Bestätigen Sie mit Einfügen und prüfen Sie anschließend durch einen Klick in die unterste Zeile des rechten Seitenfensters bei Angewendete Schritte, ob das Ergebnis angepasst worden ist oder nicht… Und Sie sehen, dass vielfach auch Korrekturen möglich sind, ohne nachfolgende Schritte erst einmal zu löschen und dann den gesamten Vorgang von vorne zu wiederholen.
Hinweis: Wenn Sie den Interessenten Winter zu einem Kunden gemacht haben, dann wird hier ein fehlerhaftes Ergebnis angezeigt. Denn es gab zu Beginn einen Kunden und einen Interessenten mit dem Namen Winter. In solchen Fällen muss der zu einem Kunden „aufgestiegene”, ehemaige Interessent Winter entweder einen Namens-Zusatz bekommen oder Sie haben dem Intermezzo folgend eine ID-Spalte eingefügt, dann bleibt der zweite Name so wie er ist und Sie entfernen die Duplikate natürlich in der ID-Spalte.
Wechseln Sie nun zum Duplikat der gerade erstellten Abfrage für das 2. Halbjahr. Auch hier werden sie den Zeitraum durch einen Filter eingrenzen. Und nun sind auch Lieferanten dabei. – Später werden Sie gewiss in der Lage sein, entweder diese beiden Queries zu kombinieren oder gleich eine einzige Abfrage zu gestalten. Im Moment reicht es, wenn Sie nach Schließen & laden die beiden Tabellen per Hand zusammenführen.