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 dieses Tutorials werden Sie die folgende Beispieltabelle verwenden.
Screenshot der 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
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.
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 den Vorgang „Gruppieren nach“ durchzuführen.
- Wählen Sie Gruppieren nach auf der Registerkarte Home .
- Wählen Sie die Option Erweitert, damit Sie mehrere Spalten für die Gruppierung auswählen können.
- Wählen Sie die Spalte Land .
- Wählen Sie Gruppierung hinzufügen.
- Wählen Sie die Spalte Vertriebskanal .
- In Neuer Spaltenname, geben Sie Gesamteinheiten ein, in Vorgang, markieren Sie Summe, und in Spalte, markieren Sie Einheiten.
- Wählen Sie OK aus.
Diese Operation ergibt die folgende Tabelle.
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.
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.
Verwenden Sie die folgenden Spalten als Gruppieren nach Spalten:
- Land / Region
- Sales Channel
Erstellen Sie zwei neue Spalten, indem Sie die folgenden Schritte ausführen:
- Aggregieren Sie die Spalte Einheiten mit Hilfe der Operation Summe. Benennen Sie diese Spalte Einheiten insgesamt.
- 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 Leerraum 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.
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 Produkte entfernt und den Datentyp für die beiden neu erweiterten Spalten festgelegt haben, sieht Ihr Ergebnis wie folgt aus.
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. Die minimale Einstellung von 0 bewirkt, dass alle Werte zusammen gruppiert werden. Bei der maximalen Einstellung von 1 werden nur Werte, die genau übereinstimmen, gruppiert. Der Standardwert ist 0,8.
- Schreibung ignorieren: Beim Vergleich von Textzeichenfolgen 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. 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 dieselben Spalten und Spaltennamen hat, wie in der vorherigen Abbildung gezeigt (sie müssen mit „Von“ und „Bis“ beschriftet werden). Andernfalls erkennt Power Query die Tabelle nicht als Transformationstabelle.
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 dazu, wie Transformationstabellen funktionieren, erhalten Sie unter Grundsätze der Transformationstabelle.
Hinzufügen einer benutzerdefinierten Spalte
Entfernen von Duplikaten