PQ: Gruppieren, Gruppieren …

Der Menüpunkt Gruppieren, ein wahres Multitalent 😎 

In Power Query hat der Menüpunkt Gruppieren recht wenig Ähnlichkeit mit der gleichnamigen Menü-Auswahl in einer Excel-Tabelle. Am besten schieben Sie erst einmal das komplette Wissen (excelseitig) beiseite und bauen auf der Basis Null auf. Sie werden erkennen, dass sie hier vieles erreichen können, was unter Excel doch einiger Klimmzüge bedarf.

Laden Sie zu Beginn erst einmal diese Datei, wo 100 Datensätze erfasst sind. Sie erkennen die Produktnummer, ein Datum und die Anzahl verkaufter Produkte. Mit unterschiedlichen kleinen Aufgaben mache ich Sie gleich mit dem Gruppieren in Power Query bekannt.

Wie Sie es eigentlich immer tun sollten, formatieren sie die Daten zuvor Als Tabelle. Ich nutze dazu entweder die Tastenkombination StrgT (Tabelle) oder StrgL (Liste) aber auch der Weg über den Menüpunkt ist gleichwertig. Und wenn eine beliebige Zelle in den Daten markiert ist, klappt das auch hervorragend. 😉 Anschließend Menü Daten, Gruppe Abrufen und transformieren | Aus Tabelle. Der Abfrage-Editor öffnet sich und stellt sich und die Daten so dar:

Der Abfrage-Editor direkt nach dem Import der Daten

Der Abfrage-Editor direkt nach dem Import der Daten

Auch wenn das Aussehen so bleiben könnte und und die gleich vorgenommene Änderung auch keinen direkten Einfluss auf weitere Berechnungen hat, mich stört einfach die Darstellung und der Datentyp der Spalte Datum. Ich mag nicht, wenn dort stets die Uhrzeit (00:00) mit angezeigt wird. Darum ein Klick in die Überschrift und beispielsweise in der Gruppe Transformieren den Datentyp auf (nur) Datum ändern.

Grundsätzlich sind damit die Vorbereitungen beendet. Als ersten Schritt sollten Sie diese Abfrage sichern. Sie werden ganz normal auf die Schaltfläche Schließen & laden klicken, damit jede neu erstellte Tabelle automatisch in ein neues Arbeitsblatt geschrieben wird.

Öffnen Sie nun wieder die Abfrage indem sie beispielsweise einen Doppelklick auf die Abfrage Tabelle1 im rechten Seitenfenster durchführen. Andere Wege führen natürlich auch zum gleichen Ziel. Im Normalfall werden sie jetzt ein bestimmtes Ziel haben, die Daten in einer definierten Form auszuwerten. Ich nehme Ihnen diese Überlegungen ab und werde einige exemplarische Auswertungen mit Ihnen gemeinsam durchgehen.

▲ nach oben …

Wie oft wurde jenes Produkt jeweils verkauft?

Vielleicht werden sie jetzt als erstes an die Excel-Funktion SUMMEWENNS() denken und ja, diese Funktion würde zum Ziel führen. Da sich hier aber alles um Power Query dreht, ist dieses natürlich ein anderer Weg…

Die Basis ist natürlich die eben erstellte Abfrage. Wenn Sie jetzt an den Quell-Daten Änderungen vornehmen, wird natürlich diese Abfrage entsprechend angepasst, spätestens nach einem Klick auf Aktualisieren. Da sie aber die Basis-Abfrage in der eben gespeicherten Form (Tabelle1) öfter einmal verwenden werden, erstellen Sie für jene neue Auswertung oder Berechnung eine weitere Kopie. Dazu klicken Sie in der Gruppe Abfrage auf Verwalten und sie haben 2 Möglichkeiten, eine Kopie zu erstellen. Mit Duplizieren wird ein Duplikat des derzeitigen Zustandes erstellt, welches sich auch nicht verändert, selbst wenn sich (in diesem Fall) Tabelle1 verändert. Wenn sich -aus welchen Gründen auch immer- die Quelldaten einmal ändern, bleibt diese Kopie also im derzeitigen Zustand. Wählen Sie jedoch den Menüpunkt Verweis, dann werden die Daten in der neu erstellten Abfrage automatisch an die Daten der Quelle (auch hier: Tabelle1) angepasst und stets aktualisiert.

Um jetzt die in der Überschrift beschriebenen Berechnung vorzunehmen, Erstellen Sie entweder ein Duplikat oder einen Verweis; sie entscheiden, was in diesem Falle für sie korrekt ist. Nun vollführen sie folgende Schritte:

  • Die Spalte Produkt-Nr. ist markiert.
  • Nun Gruppe Transformieren | Gruppieren nach.
  • Es öffnet sich dieses Fenster:

Das Dialogfenster Gruppieren direkt nach dem Öffnen

Das Dialogfenster Gruppieren direkt nach dem Öffnen

  • Die vorgegebenen Einstellungen sind hier (ausnahmsweise einmal) richtig, aber sie sollten sich dennoch die einzelnen Felder und deren Vorgaben einmal ansehen.
  • Gruppieren nach: Hier wird automatisch in der Zeile eingetragen, die beim Aufruf markiert war.
  • Neuer Spaltenname: Naja, hier habe ich es der Bequemlichkeit halber einfach dabei belassen. Es geht ja um die Anzahl der Verkäufe für das entsprechende Produkt.
  • Vorgang: Zeilen zählen ist hier korrekt, weil Power Query im Vorwege die Spalte Produkt-Nr. gruppiert und die Anzahl der Zeilen für jedes Produkt berechnet.
  • Das Feld Spalte können Sie nicht auswählen, weil das in diesem Kontext nicht zielführend wäre.

Sie erkennen, dass die zuvor als Kopie erstellte Tabelle nun auf 10 Zeilen geschrumpft ist. Und wenn sie die Daten weiter in dieser Form verwenden wollen, können sie nicht in die Ursprungsform mit den 100 Zeile zurückverwandelt werden. Darum ist es gut, dass die Basisdaten (Tabelle1) immer noch existieren.

Normalerweise hat sich links jetzt ein Seitenfenster aufgetan. Markieren Sie dort den Eintrag Tabelle1 (2), F2 und ändern Sie den Namen auf Anzahl Verkäufe je Produkt. Wenn Sie möchten, können Sie immer noch die Überschrift der 2. Spalte anpassen. Ansonsten an dieser Stelle ein Klick auf Schließen & laden und es wird ein neues Tabellenblatt mit den Daten dieser Abfrage erstellt. – Wenn Sie möchten, können Sie die komplette Tabelle (Liste) ausschneiden und an beliebiger anderer Position positionieren. Die Daten würden dennoch immer wieder aktualisiert werden.

▲ nach oben …

Summe aller Verkäufe je Produkt

Eben haben Sie berechnet, wie oft jedes Produkt verkauft worden ist. Jetzt geht es darum, wie viel Stück jedes Artikels verkauft worden sind. Die ersten Schritte sind praktisch identisch:

  • Öffnen Sie den Abfrage-Editor beispielsweise durch Rechtsklick auf eine der beiden Abfragen im rechten Seitenfenster und dort Bearbeiten.
  • Markieren Sie (bei Bedarf) im linken Seitenfenster Abfrage Tabelle1.
  • Erstellen Sie eine Kopie nach Ihren Wünschen; sie erinnern sich über die Gruppe Abfrage | Verwalten und dann entweder Duplizieren oder Verweis.
  • Der Klarheit wegen können Sie auch jetzt schon in der Abfrage einen neuen Namen geben, beispielsweise Verkauf-Summe.
  • Achten Sie darauf, dass die 1. Spalte markiert ist.
  • Gruppieren nach und belassen Sie es bei der Produktnummer, die gruppiert werden soll.
  • Bei Neuer Spaltenname geben Sie beispielsweise ein: Verkauft (Summe).
  • Bei Vorgang erweitern Sie das Dropdown und wählen Sie Summe.
  • Bei Spalte ist allerdings ein Wert vorgegeben, der keineswegs summiert werden soll. Wählen Sie hier im Dropdown Stück und bestätigen Sie mit OK.
  • Falls Sie eher ein ordnungsliebender Mensch sind, können Sie das Feld Produkt-Nr. noch aufsteigend sortieren, dann muss das nicht jedes Mal neu in der erstellten Tabelle geschehen.
  • Zum Schluss noch Schließen & laden.

Auch hier wurde naturgemäß eine 10-zeilige Tabelle erstellt und die Werte in der 2. Spalte entsprechenden der Summe der Stück-Zahlen, die in dieser Spalte vermerkt waren.

Verkäufe auf der Basis Datum

Stück je Tag

Als erstes sollten Sie die fast identische Abfrage wie gerade zuvor erstellen, nur dass dieses Mal nicht die Produktnummer sondern die Anzahl verkaufter Produkte je Tag gefragt ist. Das Vorgehen ist genau wie eben, nur dass sie bei Gruppieren nach das Datum als Parameter wählen. Und der Spaltenname sollte naturgemäß auch dem Vorgang angepasst sein; ich empfehle Umsatz (Summe) je Tag. Das nun 66 Zeilen angezeigt werden ist dem Umstand geschuldet, dass in der Ursprungsliste 66 unterschiedliche Tage aufgeführt sind. – Ich benenne diese Abfrage Tages-Umsätze. Nun können Sie natürlich noch nach Belieben sortieren, ob das Datum oder die Anzahl der Verkäufe je Tag relevant sind oder vielleicht beides.

Stück und Produkt je Tag

Natürlich werden sie zu Beginn erst einmal wieder eine Kopie der Basis-Daten aus Tabelle1 erstellen. Und bei der Gelegenheit können Sie diese Kopie auch gleich umbenennen: Datum-Produkt-Stück. Hier stellt sich die Frage: wie soll sich das Ergebnis darstellen? Je Datum wurde welches Produkt wie oft verkauft oder jedes Produkt wurde an welchem Datum wie oft verkauft? Lassen Sie sich einfach überraschen und vollziehen sie Schritt für Schritt das nach, was ich Ihnen vorschlage …

  • Die Kopie der Tabelle1 ist ja die aktuelle Tabelle, darum bedarf es keiner weiteren Schritte an dieser Stelle.
  • Egal welche Spalte gerade markiert ist: Gruppieren nach.
  • Markieren Sie zu Beginn den Punkt Erweitert statt Standard.
  • Weiter unten wählen Sie bei Gruppieren nach das Feld Datum.
  • Direkt darunter ein Klick in das Feld Gruppierung hinzufügen und belassen Sie es dort bei der Produkt-Nr.
  • Bei Neuer Spaltenname können Sie es bei Anzahl belassen oder sie tragen Summe ein.
  • Da sie ja von jedem Produkt die verkaufte Anzahl an jedem Tag berechnen werden, wählen Sie natürlich die Summe. Und die wählen Sie bei Vorgang.
  • Naturgemäß werden sie bei Spalte jetzt die Auswahl Stück treffen und anschließend mit OK abschließen.
  • Da hier das Datum das Maß der Dinge ist und erst anschließend das Produkt sortieren sie zu Beginn die Spalte Datum und anschließend die Spalte Produkt-Nr., beide aufsteigend. Und beachten Sie bitte die Reihenfolge, in die ist anders als in Excel. Die Abfrage sollte sich nun so darstellen:

Die nach Datum und Produkt sortierte Abfrage

Die nach Datum und Produkt sortierte Abfrage

  • Speichern Sie die Abfrage unter einem beliebigen Namen. Sollten Sie die gerade eben erstellten Abfragen nicht gespeichert haben (ich hatte es ja nicht eingewiesen), dann werden diese jetzt auch gleich in jeweils eine Tabelle geschrieben.
  • Öffnen Sie noch einmal diese Abfrage und machen Sie sich klar, wie die Daten hier dargestellt werden: an unterschiedlichen Tagen des gespeicherten Zeitbereichs wurden Produkte verkauft. Teilweise nur ein Produkt (beispielsweise am 6. August), teilweise auch mehrere. Die Basis dieser Abfrage ist also das Datum.

  • Um als Priorität das Produkt darzustellen, erstellen Sie von exakt dieser Abfrage eine Kopie und geben hier beispielsweise den Namen Produkt-Datum-Stück. Übrigens: dass kopieren geht auch per Rechtsklick im linken Seitenfenster auf den Namen der Abfrage und dann in Kontextmenü die entsprechende Auswahl treffen. Und natürlich gleich auch diese Abfrage umbenennen, damit sie nicht durcheinander gekommen. 😉
  • Die Daten an sich stimmen ja, nur die Reihenfolge der Darstellung und auch die Sortierung passen nicht zu der Forderung. Darum verschieben sie erst einmal die Spalte Datum an die 2. Stelle, indem sie in die Überschrift klicken und die Spalte dann nach rechts ziehen.
  • Wenn Sie jetzt einfach neu sortieren, werden sie vermutlich Schiffbruch erleiden. Es ist wichtig, dass sie beiden Spalten zu Beginn im Sortier-Dropdown den Punkt Sortierung löschen wählen, damit der Ursprungszustand wiederhergestellt wird.
  • Nacheinander jetzt zuerst die Produkt-Nr. aufsteigend und dann auch die Spalte Datum aufsteigend sortieren.
  • Speichern Sie nun diese 6. Abfrage unter einem Ihnen genehmen Namen.

▲ nach oben …

Die besten Verkaufszahlen je Tag

An diversen Tagen sind von ja unterschiedliche Artikel in verschiedener Anzahl verkauft worden. Zugegeben, die verkauften Stückzahlen eines nicht definierten Artikels sind nicht wirklich aussagekräftig, aber wenn sie sich vorstellen, dass die angegebene Zahl jeweils für 1.000 € steht, dann klingt das ganze schon viel besser. 😉 Aber das wird nicht hier zu diskutieren sein, es geht ja um das Prinzip.

  • Kopieren Sie erst einmal wieder die Tabelle1. Und um es zur Gewohnheit werden zu lassen, vergeben Sie auch gleich einen passenden Namen; entweder Beste Stückzahlen je Tag oder beispielsweise maximal Umsätze / Tag. Sie merken: Hier ist auch der/in Abfragenamen erlaubt.
  • Gruppieren nach und natürlich soll nach dem Datum gruppiert werden.
  • Der Spaltenname könnte Maximum oder ähnlich lauten.
  • Als Vorgang wählen Sie natürlich Max.
  • Und wie immer soll natürlich die Spalte Stück als Basis für die Berechnung dienen.
  • Da diese neu erstellte Abfrage ja noch unberührt ist, können Sie ohne weiteres nach der Spalte Maximum absteigend sortieren. So stehen die besten Zahlen oben.

Prinzipiell ist auch diese Aufgabe damit erfüllt. Wenn Sie aber nur die Top Ten Werte darstellen wollen, dann klicken Sie im Menüband auf Zeilen verringern | Zeilen beibehalten | Erste Zeilen beibehalten und geben dann bei Anzahl von Zeilen beispielsweise die 10 für die 10 besten Werte ein. Aber ich habe an dieser Stelle bei der jetzigen Power Query Version eine böse Überraschung erlebt: obwohl die Sortierung ganz klar sichtbar war sind nach dem Entfernen der Zeilen mit Ausnahme der ersten 10 zwar die korrekte Zahl von Zeilen stehen geblieben aber es waren keineswegs die höchsten Werte (50..46) sondern genau jene, die ohne die Sortierung dort stehen würden. Ich habe keine Ahnung, warum dieser Bug ab und zu auftritt. Ich habe das Ganze noch einmal durchgeführt und da kam es dann auch zum erwarteten und gewünschten Erfolg. Nun ja…

▲ nach oben …

Weitere Möglichkeiten

Sie haben in diesem kleinen Beitrag die wichtigsten Möglichkeiten des Menüpunkts Kombinieren kennengelernt. Schauen Sie sich bitte insbesondere die weiteren Möglichkeiten bei Vorgang an und probieren Sie auch gerne einmal mit den Erweitert – Möglichkeiten herum. Dort spielt die Reihenfolge der Auswahl eine entscheidende Rolle.

Bedenken Sie, dass jede auf diese Weise erstellte Abfrage eine einzelne, selbstständige Query ist. Sie haben in diesem kleinen Ausflug erlebt, dass eine von Ihnen erstellte Abfrage noch einmal kopiert worden ist und als Basis für andere Darstellung genutzt wurde. Das gilt übrigens für jede Abfrage, egal ob sie diese gespeichert haben oder nicht.

▲ nach oben …

Dieser Beitrag wurde unter Filtern & Sortieren, Power Query, PQ-Basics abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.