Zeilen gruppieren oder zusammenfassen

In Power Query können Sie Werte in verschiedenen Zeilen zu einem einzigen Wert gruppieren, indem Sie die Zeilen nach den Werten in einer oder mehreren Spalten gruppieren. Sie können zwischen zwei Arten von Gruppierungsvorgängen wählen:

• Säulengruppierungen.

• Zeilengruppierungen.

Für diesen Lehrgang werden Sie die folgende Beispieltabelle verwenden.

Tabelle mit Spalten, die das Jahr (2020), das Land (USA, Panama oder Kanada), das Produkt (Hemd oder Shorts), den Vertriebskanal (Online oder Händler) und die Einheiten (verschiedene Werte von 55 bis 7500) anzeigen

Wo Sie die Schaltfläche Gruppieren nach finden

Die Schaltfläche Gruppe finden Sie unter an drei Stellen:

• Auf der Registerkarte Home , in der Gruppe Transform .

  

• Auf der Registerkarte Transformieren, in der Gruppe Tabelle.

  

• Im Kontextmenü, wenn Sie mit der rechten Maustaste klicken, um Spalten auszuwählen.

  

Verwenden Sie eine Aggregatfunktion zum Gruppieren nach einer oder mehreren Spalten

In diesem Beispiel ist Ihr Ziel, die Gesamtzahl der verkauften Einheiten auf Länder- und Vertriebskanalebene zusammenzufassen. Sie werden die Spalten Land und Vertriebskanal verwenden, um die Gruppierung nach durchzuführen.

1. Wählen Sie Gruppieren nach auf der Registerkarte Home .
2. Wählen Sie die Option Erweitert , damit Sie mehrere Spalten für die Gruppierung auswählen können.
3. Wählen Sie die Spalte Land .
4. Wählen Sie Gruppierung hinzufügen.
5. Wählen Sie die Spalte Vertriebskanal .
6. In Neuer Spaltenname, geben Sie Gesamteinheiten ein, in Vorgang, markieren Sie Summe, und in Spalte, markieren Sie Einheiten.
7. Klicken Sie auf OK.

Diese Operation ergibt die folgende Tabelle.

Verfügbare Operationen

Mit der Funktion Gruppieren nach können die verfügbaren Vorgänge auf zwei Arten kategorisiert werden:

• Betrieb auf Zeilenebene
• Betrieb auf Säulenebene

In der folgenden Tabelle wird jeder dieser Vorgänge beschrieben.

Vorgangsname	Category	Beschreibung
Sum	Betrieb der Säule	Summiert alle Werte aus einer Spalte
Mittelwert	Betrieb der Säule	Berechnet den Durchschnittswert aus einer Spalte
Median	Betrieb der Säule	Berechnet den Median aus einer Spalte
Min	Betrieb der Säule	Berechnet den Minimalwert aus einer Spalte
Max	Betrieb der Säule	Berechnet den Maximalwert aus einer Spalte
Perzentil	Betrieb der Säule	Berechnet das Perzentil anhand eines Eingabewertes von 0 bis 100 aus einer Spalte
Eindeutige Werte zählen	Betrieb der Säule	Berechnet die Anzahl der eindeutigen Werte aus einer Spalte
Count -Zeilen	Reihenbetrieb	Berechnet die Gesamtzahl der Zeilen aus einer bestimmten Gruppe
Eindeutige Zeilen zählen	Reihenbetrieb	Berechnet die Anzahl der eindeutigen Zeilen aus einer bestimmten Gruppe
Alle Zeilen	Reihenbetrieb	Gibt alle gruppierten Zeilen in einem Tabellenwert ohne Aggregationen aus

Hinweis

Die Operationen Count distinct values und Percentile sind nur in Power Query Online verfügbar.

Eine Operation zur Gruppierung nach einer oder mehreren Spalten durchführen

Ausgehend vom ursprünglichen Beispiel erstellen Sie in diesem Beispiel eine Spalte mit den Gesamteinheiten und zwei weitere Spalten, die den Namen und die verkauften Einheiten für das leistungsstärkste Produkt enthalten, zusammengefasst auf der Ebene des Landes und des Vertriebskanals.

1. Verwenden Sie die folgenden Spalten als Gruppieren nach Spalten:

   • Land / Region
   • Sales Channel

2. Erstellen Sie zwei neue Spalten, indem Sie die folgenden Schritte ausführen:

   1. Aggregieren Sie die Spalte Einheiten mit Hilfe der Operation Summe. Benennen Sie diese Spalte Einheiten insgesamt.
   2. Fügen Sie eine neue Spalte Produkte hinzu, indem Sie die Operation Alle Zeilen verwenden.

   

Nachdem dieser Vorgang abgeschlossen ist, sehen Sie, dass die Spalte Produkte in jeder Zelle [Tabelle] Werte enthält. Jeder [Tabelle]-Wert enthält alle Zeilen, die nach den Spalten Land und Vertriebskanal aus Ihrer ursprünglichen Tabelle gruppiert wurden. Sie können den weißen Bereich innerhalb der Zelle auswählen, um eine Vorschau des Tabelleninhalts am unteren Rand des Dialogfelds anzuzeigen.

Hinweis

Im Detailvorschaufenster werden möglicherweise nicht alle Zeilen angezeigt, die für die Gruppierungsoperation verwendet wurden. Sie können den Wert [Tabelle] auswählen, um alle Zeilen zu sehen, die zu der entsprechenden Gruppierungsoperation gehören.

Als Nächstes müssen Sie die Zeile mit dem höchsten Wert in der Spalte Einheiten der Tabellen in die neue Spalte Produkte extrahieren und diese neue Spalte Top Performer Produkt nennen.

Extrahieren Sie die Informationen zu den leistungsstärksten Produkten

Mit der neuen Spalte Produkte mit [Tabelle]-Werten erstellen Sie eine neue benutzerdefinierte Spalte, indem Sie in der Multifunktionsleiste auf die Registerkarte Spalte hinzufügen gehen und Benutzerdefinierte Spalte in der Gruppe Allgemein auswählen.

Benennen Sie Ihre neue Rubrik Leistungsstärkstes Produkt. Geben Sie die Formel Table.Max([Products], "Units" ) unter Benutzerdefinierte Spaltenformel ein.

Das Ergebnis dieser Formel erzeugt eine neue Spalte mit [Record]-Werten. Diese Datensatzwerte sind im Wesentlichen eine Tabelle mit nur einer Zeile. Diese Datensätze enthalten die Zeile mit dem Höchstwert für die Spalte Einheiten für jeden [Tabellen-]Wert in der Spalte Produkte .

Mit dieser neuen Spalte Top-Performer-Produkt, die [Datensatz]-Werte enthält, können Sie das Symbol erweitern, die Felder Produkt und Einheiten auswählen und dann OK wählen.

Nachdem Sie die Spalte Products entfernt und den Datentyp für die beiden neu erweiterten Spalten festgelegt haben, sieht Ihr Ergebnis wie folgt aus.

Unscharfe Gruppierung

Hinweis

Die folgende Funktion ist nur in Power Query Online verfügbar.

Zur Veranschaulichung der „unscharfen Gruppierung“ sehen Sie sich die Beispieltabelle in der folgenden Abbildung an.

Das Ziel der unscharfen Gruppierung ist es, eine Gruppierungsoperation durchzuführen, die einen Algorithmus für die ungefähre Übereinstimmung von Textzeichenfolgen verwendet. Power Query verwendet den Jaccard-Ähnlichkeitsalgorithmus, um die Ähnlichkeit zwischen Paaren von Instanzen zu messen. Dann wendet es das agglomerative hierarchische Clustering an, um Instanzen zusammenzufassen. Die folgende Abbildung zeigt die zu erwartende Ausgabe, bei der die Tabelle nach der Spalte Person gruppiert wird.

Für die unscharfe Gruppierung führen Sie dieselben Schritte aus, die bereits in diesem Artikel beschrieben wurden. Der einzige Unterschied besteht darin, dass Sie dieses Mal im Dialogfeld Gruppieren nach das Kontrollkästchen Unscharfe Gruppierung verwenden aktivieren.

Für jede Gruppe von Zeilen wählt Power Query die häufigste Instanz als „kanonische“ Instanz aus. Wenn mehrere Instanzen mit der gleichen Häufigkeit auftreten, wählt Power Query die erste aus. Nachdem Sie OK im Dialogfeld Gruppieren nach ausgewählt haben, erhalten Sie das erwartete Ergebnis.

Sie haben jedoch mehr Kontrolle über die Fuzzy-Gruppierung, indem Sie Fuzzy-Gruppenoptionen erweitern.

Für die unscharfe Gruppierung stehen die folgenden Optionen zur Verfügung:

• Ähnlichkeitsschwelle (optional): Diese Option gibt an, wie ähnlich zwei Werte sein müssen, damit sie in einer Gruppe zusammengefasst werden. Bei der Mindesteinstellung von 0 werden alle Werte in einer Gruppe zusammengefasst. Bei der maximalen Einstellung von 1 werden nur Werte zusammengefasst, die genau übereinstimmen. Der Standardwert ist 0,8.
• Fall ignorieren: Beim Vergleich von Textstrings wird die Groß- und Kleinschreibung ignoriert. Diese Option ist standardmäßig aktiviert.
• Gruppieren durch Kombinieren von Textteilen: Der Algorithmus versucht, Textteile zu kombinieren (z. B. Micro und soft zu Microsoft), um Werte zu gruppieren.
• Ähnlichkeitswerte anzeigen: Zeigen Sie die Ähnlichkeitswerte zwischen den Eingabewerten und den berechneten repräsentativen Werten nach der Fuzzy-Gruppierung an. Erfordert die Hinzufügung eines Vorgangs wie Alle Zeilen, um diese Informationen zeilenweise darzustellen.
• Transformationstabelle (optional): Sie können eine Transformationstabelle auswählen, die Werte zuordnet (z. B. Zuordnung von MSFT zu Microsoft), um sie zusammenzufassen.

In diesem Beispiel wird eine Transformationstabelle verwendet, um zu zeigen, wie Werte abgebildet werden können. Die Transformationstabelle hat zwei Spalten:

• Von: Die Textzeichenfolge, nach der in Ihrer Tabelle gesucht werden soll.
• Auf: Die Textzeichenfolge, mit der die Textzeichenfolge in der Spalte von ersetzt werden soll.

Die folgende Abbildung zeigt die in diesem Beispiel verwendete Transformationstabelle.

Wichtig

Es ist wichtig, dass die Transformationstabelle die gleichen Spalten und Spaltennamen hat wie oben gezeigt (sie müssen „Von“ und „Nach“ lauten), da Power Query diese sonst nicht erkennt.

Kehren Sie zum Dialogfeld Gruppieren nach zurück, erweitern Sie Unscharfe Gruppenoptionen, ändern Sie die Operation von Zeilen zählen in Alle Zeilen, aktivieren Sie die Option Ähnlichkeitswerte anzeigen und wählen Sie dann das Dropdown-Menü Transformationstabelle.

Nachdem Sie die Transformationstabelle ausgewählt haben, wählen Sie OK. Das Ergebnis dieser Operation gibt Ihnen folgende Informationen:

In diesem Beispiel wurde die Option Groß-/Kleinschreibung ignorieren aktiviert, so dass die Werte in der Spalte von der Transformationstabelle verwendet werden, um nach der Textzeichenfolge zu suchen, ohne die Groß-/Kleinschreibung der Zeichenfolge zu berücksichtigen. Diese Umwandlung erfolgt zuerst, und dann wird die Fuzzy-Gruppierung durchgeführt.

Der Ähnlichkeitswert wird auch im Tabellenwert neben der Personenspalte angezeigt, der genau angibt, wie die Werte gruppiert wurden und welche Ähnlichkeitswerte sie jeweils haben. Sie können diese Spalte bei Bedarf erweitern oder die Werte aus den neuen Frequenzspalten für andere Arten von Transformationen verwenden.

Hinweis

Bei der Gruppierung nach mehreren Spalten führt die Transformationstabelle die Ersetzungsoperation in allen Spalten durch, wenn das Ersetzen des Wertes die Ähnlichkeitsbewertung erhöht.

Weitere Informationen

Hinzufügen einer benutzerdefinierten Spalte
Entfernen von Duplikaten