In diesem Eintrag möchte ich das Tutorial in der Einführung in PowerQuery weiterführen. Hier geht es um die Gruppierungsfunktion und das Verbinden zweier Tabellen. Hier werden wir eine Excel Datei auslesen mit 2 Tabellenblättern – eins hält die Bestellungen für das Jahr 2014 und das zweite Blatt die Informationen zu den Kunden. Ziel ist die Auswertung der Umsätze pro Kunde und pro Monat. Normalerweise würde ich das in PowerPivot lösen, aber für eine einfache Aufbereitung der Daten und deren Darstellung in diesem Kontext bietet PowerQuery eine etwas leichtere Methode – da hier gleich die Daten zum Schluss gruppiert zur Verfügung stehen und lediglich dargestellt werden müssen. Vielleicht aber auch Geschmackssache …
Als erstes wechseln wir auf das Ribbon PowerQuery und laden Daten aus einer Datei, bzw. gleich Excel.
Im nächsten Schritt befindet sich im Navigator die jeweilige Tabellen aus dem Excel Sheet. Einerseits sehen wir hier die Tabellenblätter aber auch die Tabellen die sich auf den Blättern befinden. Da sich hier jeweils eine Tabelle auf jedem Blatt befindet, wähle ich die Tabellenblätter aus. Hier setze ich gleich die Haken bei beiden, wähle aus, dass die Daten in das Datenmodell (PowerPivot) geladen werden.
Weiterhin wähle ich die erste Tabelle Bestellungen aus und klicke auf Bearbeiten.
Nun muss in beiden Tabellen jeweils die erste Zeile noch als Tabellenüberschrift gekennzeichnet werden. Nachdem dies bei den Bestellungen durchgeführt wurde, klicke ich auf Anwenden und schließen und wähle die Tabelle Kunden im Navigator aus und Bearbeite diese genauso:
Nachdem die Kundentabelle ebenfalls die Überschriften aus der ersten Zeile entnimmt, schließe ich ebenfalls das Fenster über Anwenden und schließen und klicke im Navigator auf Laden damit beide Excel Tabellen in PowerQuery verfügbar sind.
Nun müssen beide Tabellen miteinander verbunden werden …
Dazu wähle ich aus dem Navigator die Kunden Tabelle aus und klicke auf Abfragen zusammenführen:
Nun sind die Tabellen verbunden – technisch ist das wie ein Left Join zu sehen. Über den Haken „nur Übereinstimmende Zeilen einbeziehen“ würde das einem Inner Join entsprechen.
Nun können die Spalten aus der rechten Tabellen hinzugefügt werden.
Da ich hier lediglich die Bestellungen aus dem Jahr 2014 vor mir habe und das Ziel eine Auswertung pro Monat ist, transformiere ich die Spalte Bestelldatum über einen Rechtsklick in Monate um. Danach steht das Feld als Monatsnummer zur Verfügung.
Die Transformation kann nachträglich immer noch wieder zurückgestellt werden. An sich handelt es sich nur um eine Formatierung.
Im nächsten Schritt brauchen wir eine Gesamtsumme. In den Daten steht derzeit nur die Summe der Einzelposition und die Menge zur Verfügung. Daher fügen wir hier nun eine Benutzerdefinierte Spalte ein mit folgender Formel:
Nun markiere ich noch die nicht mehr benötigten Spalten und entferne diese:
Nun markiere ich die Spalten Name und Monat und klicke auf Gruppieren Nach – nun erfolgt die Gruppierung, bzw. Summierung auf den Gesamtpreis.
Nach dem Klick auf Anwenden und Schließen befindet sich im PowerPivot Datenmodell die gruppierte und aufbereitete Tabelle. Diese kann ich nun in Excel darstellen….