Eine sehr coole Funktion in Power Query ist das Gruppieren. Noch besser wird diese Funktion durch das Aggregieren von Werten, sei es Zählen, Aufsummieren, etc.
Schwieriger wird es aber, wenn man keine numerischen Aggregationswerte hat, sondern Text. Hierfür muss man die einzelnen Elemente miteinander verbinden, und im folgenden zeige ich euch, wie.
Als Add-on zeige ich noch die Möglichkeit, die Tabelle zu pivotieren, um sie für das menschliche Auge besser lesbar zu machen.
Stellt euch vor, ihr wollt eine Aufstellung von Käufern und Verkäufern haben, und übersichtlich sehen, wer was bei wem gekauft hat. So wie in dem Bild rechts.
Ich habe hier die Matrix schon als Tabelle formatiert und kann sie nun leicht per Daten / Aus Tabelle/Bereich in den Power Query Editor laden.
Da die Spalten Käufer und Verkäufer eine fixe Kombination darstellen, muss nach diesen beiden gruppiert werden. Dazu markiert ihr die beiden Spalten, und wählt dann im Start- Register „Gruppieren nach“ aus.
Im Part „Neuer Spaltenname“ könnt ihr nun die Aggregation unter „Vorgang“ aussuchen. Per Default wird hier Zeilen zählen ausgewählt, was euch, nachdem ihr OK geclickt habt, auch direkt angezeigt wird.
Für die Kombination „Imperium – Kessel“ gibt es zwei Vorgänge (im Screenshot oben Zeilen 2 + 5), für die beiden anderen jeweils einen Vorgang. Der M- Code dazu sieht in der Formelleiste so aus:
= Table.Group(#"Geänderter Typ", {"Käufer", "Verkäufer"}, {{"Anzahl", each Table.RowCount(_), Int64.Type}})Die ersten beiden geschweiften Klammern bestimmen die fixe Kombination, danach kommt mit „Anzahl“ der neue Spaltenname und die Funktion Table.RowCount zählt jeden Record, also jeden einzelnen Eintrag der Kombination.
Soweit so gut. Nun wollen wir aber weiterhin die Kategorien sehen. Dazu müssen wir die Einträge miteinander verketten. Dazu gibt es die Formel Text.Combine, die wir in die obige Formel einbauen:
= Table.Group(#"Geänderter Typ", {"Käufer", "Verkäufer"}, {{"Kategorien", each Text.Combine([Kategorie], ", ")}})Die fixe Kombination bleibt gleich, aber statt „Anzahl“ nennen wir die neue Spalte in „Kategorien“ um und sagen in der Funktion, das jeder Eintrag mit einem Komma und Leerzeichen verkettet werden soll. Damit erhalten wir folgende Tabelle:
Das sieht schon mal ganz gut aus, ist aber noch etwas schwer leserlich. Daher pivotieren wir noch die Spalte Verkäufer. Pivotieren bedeutet: aus den Listen-Einträgen (untereinander) werden Spalten (nebeneinander), sodass wir quasi eine Verkaufsmatrix erhalten.
Dazu markiert ihr die Spalte [Verkäufer] und clickt im Reiter „Transformieren“ auf „Spalte pivotieren“. Im Dialog-Feld werdet ihr nach der Wertespalte gefragt, das ist die Spalte, in der normalerweise numerische Werte stehen. In unserem Fall jedoch darf nichts aggregiert werden. Das lässt sich über die erweiterten Optionen auswählen.
Wenn ihr nun noch mit OK bestätigt, sollte euch folgende Tabelle dargestellt werden. Und hier kann man sehr sauber und ordentlich erkennen, wer welche Produkte von welchem Planeten bezogen hat.
PS: Eure Ursprungstabelle sollte keine Duplikate enthalten, also zum Beispiel noch eine Zeile mit „Imperium, Kessel, Coaxium“, da sonst die Einträge mehrfach aufgezählt werden.
Danke, danach habe ich schon ewig gesucht!