Power Query wird oft genutzt, um Informationen aus einer Zelle zu trennen und auf verschiedenen Zeilen oder Spalten zu verteilen. Doch manchmal wird genau das Gegenteil gebraucht. Diesmal muss ich Inhalte aus mehreren Zeilen in einer Zelle zusammenfassen:
- Und zwar sind links in der grauen Originaltabelle die Artikel wegen verschiedener Herkunftsländer mehrfach aufgelistet.
- Ich benötige jedoch eine Übersicht, in der alle Artikel nur einmal stehen.
- Die Herkunftsländer sollen in einer Zelle als Zusatzinformation zusammengefasst werden.
Links die Artikelliste mit Duplikaten, rechts die Liste nach ArtikelNr gruppiert und die Herkunftsländer in einer Zelle gebündelt
So ist die Originaltabelle aufgebaut
- Spalte 1 die Artikelnummer,
- Spalte 2 die Artikelgruppe und
- Spalte 3 das Herkunftsland.
Die Ausgangstabelle sortiert nach Artikelnummer und mit Duplikaten aufgrund mehrerer Herkunftsländer
Die Aufgabe
- Artikel mit gleicher Nummer und Gruppe können aus verschiedenen Herkunftsländern kommen.
- Ich brauche jedoch eine kompakte Übersicht, in der jeder Artikel nur einmal vorkommt.
- Die Herkunftsländer sollen trotzdem als Information zur Verfügung stehen.
Dazu muss ich die Tabelle nach ArtikelNr und Artikelgruppe gruppieren und die Herkunftsländer aggregieren als kommagetrennte Textkette.
So gehe ich vor
- In Excel lese ich über Daten > Daten abrufen > Aus Tabelle/Bereich die Tabelle mit dem Namen tbl_Artikel in Power Query ein.
- Ich markiere von links nach rechts mit Hilfe der Umschalt-Taste alle Spalten, halte die Umschalt-Taste gedrückt und stelle den Datentyp Text ein. Das ist wichtig für den nächsten Schritt, das Gruppieren.
Erst das Bestimmen der Datentypen macht eine Gruppierung möglich
- Anschließend markiere ich die Spalten ArtikelNr und Artikelgruppe [1] und wähle im Register Start > Gruppieren nach [2].
Die Duplikate der Spalten ArtikelNr und Artikelgruppe mittels Gruppierung zusammenführen
- Als Spalte für die Aggregation wähle ich Herkunftsland [4].
- Als Vorgang steht keine Textverkettung zu Verfügung. Daher wähle ich erstmal Summe [3] und trage als neuen Spaltennamen wieder Herkunftsland ein.
- Die Summe über eine Textspalte führt natürlich zu einem Fehler – wie im folgenden Bild zu sehen. Deshalb ersetze ich List.Sum durch Text.Combine und gebe dieser Funktion noch das Trennzeichen „, „ mit.
Mit Hilfe eines kleinen Tricks schaffe ich eine weitere Aggregationsmöglichkeit, nämlich Textverkettung
Das Ergebnis kann sich sehen lassen: Eine Artikelliste ohne Duplikate, die trotzdem noch alle Informationen beinhaltet.
Die fertige Artikelliste ohne Duplikate
Profitipp: Herkunftsländer innerhalb der Zelle noch sortieren
- Die Herkunftsländer erscheinen in der Reihenfolge, wie sie in der Ursprungsliste aufgeführt sind.
- Übersichtlicher wäre es jedoch, wenn die Herkunftsländer in jeder Zelle alphabetisch sortiert stehen. Ein kleiner Trick macht auch das möglich.
Vor der Gruppierung füge ich einen weiteren Schritt ein:
- Ich markiere unter Angewendete Schritte den Schritt Geänderter Typ [1] und wähle in der Spalte Herkunftsland [2] > Aufsteigend sortieren [3].
Die Liste vor dem Gruppieren nach Herkunftsland sortieren
- Damit diese Sortierung auch für den nächsten Schritt der Gruppierung erhalten bleibt, umschließe ich die Sortieranweisung in der Bearbeitungszeile mit der Funktion Table.Buffer().
Die Sortierung mit Table.Buffer umschließen
Wer die Liste nun wieder alphabetisch nach ArtikelNr sortiert haben möchte, kann dies als letzten Schritt über den Spaltenkopf von ArtikelNr erledigen.
De fertige Liste sortiert nach Artikelnummer
Fazit: Der Nutzen von DATA STRUCTURING
Das war ein Beispiel für DATA STRUCTURING. Was genau hat es gebracht?
a) Bessere Übersicht: Die reduzierte Liste mit den gebündelten Herkunftsländern in einer Spalte macht die Datenmenge übersichtlicher. Ohne die unnötigen Duplikate ist es leichter, gleiche oder ähnliche Artikel auf einen Blick zu erkennen.
b) Leichtere Datenanalyse: Beim Erstellen von Analysen – z. B. Umsatz-, Einkaufs- oder Lagerstatistik – lässt sich diese Liste nun als Dimensionstabelle nutzen, da sie keine Duplikate mehr aufweist.
Für alle, die mittels DATA STRUCTURING …
… mehr aus ihren Daten herausholen oder einfach nur die Qualität der Daten verbessern wollen, gibt es die nächste Gelegenheit am Vormittag des 10. Oktober beim Kurs Power Query: Bessere Datenqualität mit DATA STRUCTURING
Per Klick auf das Bild geht es zur Webseite mit allen Kurs-Details
