Umsatz-Liste zu Kreuztabelle mit Power Query (2)

Foren-Q&A: Umsatz-Analyse mit Power Query und transponieren der Daten (Teil 2)


Video auf YouTubeHinweis:
Die Arbeitsschritte dieses Beitrages sind in einem kleinen Video dokumentiert. Das unterstützende Begleit-Video finden Sie auf YouTube an dieser Stelle.

Beachten Sie bitte: Dieses Begleit-Video enthält zusätzliche Informationen, die im folgenden Beitrag nicht dokumentiert sind!


Auch wenn jeder dieser 3 Teile in sich abgeschlossen ist, werde ich mich in den ersten Zeilen dieses 2. Teils etwas knapper fassen; zumal hier auch die Basisdaten schon etwas aufbereitet sind. Dennoch … Es sind die gleichen Daten; teils gekürzt, teils ergänzt. Laden Sie diese einfach einmal hier herunter. Was in diesem File komplett fehlt ist das Arbeitsblatt mit dem Muster, wie es anschließend nach der Fertigstellung sein sollte. Da könnten Sie zum Vergleich den vorherigen Beitrag zum Thema öffnen oder Sie glauben mir einfach, dass das Ergebnis stimmt. 😉

Im Blatt-Register Roh-Daten fällt Ihnen gewiss auf, dass hier im Vergleich zur vorherigen Version nur 2 Spalten vorhanden sind, es fehlen also die Monatsnamen in der Kurzschreibweise. Diese Änderung habe ich bewusst vorgenommen.

Und im 2. Arbeitsblatt, welches hier den Namen Monate hat, habe ich bereits 2 Tabellen vorbereitet. Beide enthalten die Monatsnamen, einmal in Lang- und einmal in Kurzschreibweise. Und jede dieser beiden Tabellen hat auch eine sinnvolle, eine „sprechende“ Bezeichnung/Überschrift.

Apropos Monatsnamen: In dieser Übung habe ich nur die Kurzform mit den 3 Zeichen verwendet. Für Ihre persönlichen Bedürfnisse können Sie natürlich problemlos auch die andere Tabelle mit den ausgeschriebenen Namen verwenden.

Zu Beginn werden sie naturgemäß die Tabelle mit den Roh-Daten in den Abfrage-Editor importieren. Je nach Version geht das über den Menüpunkt Power Query oder Daten. Und natürlich holen sie sich die Daten aus einer Tabelle. Das Ergebnis stellt sich anschließend so dar:

Die Daten im Editor nach dem Import

Die Daten im Editor nach dem Import

Gleich zu Beginn wechseln Sie in das Menü Spalte hinzufügen und erstellen eine Benutzerdefinierte Spalte. Als Neuer Spaltenname verwenden Sie vorzugsweise (nur) M, weil in dieser Spalte die Monatszahl numerisch ohne führende Null dargestellt werden wird. Per Tab oder Mausklick kommen sie in den Bereich Benutzerdefinierte Spaltenformel und geben Sie dort nach dem fest eingefügten = folgende Formel ein: Date.Month([Datum]) wobei sie auf die exakte Groß- Kleinschreibung achten müssen. Beim Feldnamen [Datum] können Sie sich die Arbeit etwas erleichtern, indem sie in der rechten Box einen Doppelklick auf genau dieses Element durchführen, der Wert wird dann automatisch mit den eckigen Klammern in die Formel eingefügt.

Bestätigen Sie mit OK und umgehend wird die numerische Bezeichnung des Monats aus der Spalte Datum in die Spalte M eingetragen.

Sie erinnern sich vielleicht, dass in der zu erzeugenden Kreuztabelle in jede Monats-Spalte die Anzahl der Aufträge für den entsprechenden Monat eingetragen werden soll. Da in der Vorlage (den Originaldaten) keinerlei Zahl für das jeweilige Produkt vermerkt war, habe ich einfach festgelegt, dass es immer genau ein Stück ist. Schließlich stand auch in der Ergebnis-Vorlage immer nur die 1 bzw. 0. Generieren Sie nun eine weitere Benutzerdefinierte Spalte, verwenden als Überschrift beispielsweise Zähler und als Benutzerdefinierte Formel schreiben Sie nur die Zahl 1 in das Feld. Damit bekommt jeder Auftrag, jede Zeile in dieser Spalte automatisch den Wert 1.

Damit ist die erste Abfrage prinzipiell fertig gestaltet. Wechseln Sie zum Menü Datei | Schließen & laden in… und im Dialog klicken Sie auf Nur Verbindung erstellen. Anschließend Laden. Damit erreichen Sie, dass diese Abfrage nicht noch einmal in einem getrennten Excel-Tabellenblatt als Tabelle geschrieben wird und damit unnötige Redundanzen erzeugt werden.

Wechseln Sie nun in das Arbeitsblatt Monate und importieren Sie die Tabelle mit den Kurzbezeichnungen der Monate. Im Abfrage-Editor sind nun alle 12 Monate in der korrekten Reihenfolge untereinander aufgeführt. Um sie später auch Power Query korrekt sortieren zu können, bedarf es einer kleinen „Krücke“: Spalte hinzufügen und erweitern Sie in der Gruppe Allgemein den Punkt Indexspalte durch einen Klick auf das kleine Dreieck ▼. Wählen Sie nun im Dropdown-Menü den Punkt Von 1, damit wird jedem Monat auf die einfache Art und Weise der korrekte numerische Werte zugewiesen.

Was mich an dieser Stelle noch stört ist der durch Excel vergebene Name der Tabelle und damit auch der Name der Abfrage. Ändern Sie ihn von Tabelle1 auf beispielsweise Monate. Danach sichern Sie den Stand der Dinge und nach Schließen & laden sind sie erst einmal wieder im Arbeitsblatt Monate

Zugegeben, das speichern hätte eben und an der Stelle noch nicht sein müssen. Aber eine kleine Sicherheitsmaßnahme kann nie schaden. 💡 Öffnen Sie nun wiederum die Abfrage Monate und klicken Sie in der Menüleiste auf das Symbol Kombinieren. Danach bitte gleich Abfragen zusammenführen auswählen. Es öffnet sich ein Dialog, der sich so darstellt:

Klicken Sie in das noch leere Kombinationsfeld oberhalb der Box Es ist keine Vorschau verfügbar und wählen Sie im DropDown des Kombinationsfeldes anschließend die Abfrage RawData. Sie sehen, dass sofort in der unteren Hälfte des Dialogs die ersten Zeilen dieser Query sichtbar sind.

Der nächste Schritt ist für Einsteiger immer etwas abstrakt, muss aber sehr genau durchgeführt werden. In beiden Abfragen gibt es eine Spalte, die als Verknüpfung des jeweiligen Elements fungieren muss. Es müssen die gleich(artig)en Daten sein, welche sie per Klick in die Daten einer Spalte markieren. Beispielsweise immer die Kundennummern, wobei die Bezeichnung die Spalte (Überschrift) durchaus unterschiedlich sein kann.

Ich weiß, dass hört sich ziemlich verschroben an. 😳 Wenn Sie einmal die Spaltennamen außer Acht lassen, dann hilft Ihnen vielleicht dieser Hinweis: In der Abfrage RawData steht in der Zeile Produkt1 eine 3 in der Spalte M, weil dieser Wert ja durch Power Query aus dem Datum der Spalte Datum berechnet worden ist. Wenn Sie nun den Wert 3 in der oberen Hälfte des Dialogs suchen werden Sie feststellen, dass dort Mrz in der Spalte links daneben steht. Und das passt dann logischerweise auch. Darum ein Klick in die Daten bei Index und ein Klick in die Daten der Spalte M, weil in jeder dieser Spalten der numerische Monatswert steht. Beide sind dann entsprechend markiert und im Fußbereich des Dialogs erkennen sie den Hinweis, dass die Auswahl mit 7 der ersten 12 Zeilen übereinstimmt:

Die zu verlinken den Spalten sind markiert

Schließen Sie nun diesen Dialog und wieder im Abfrage-Editor erweitern Sie die Spalte RawData durch einen Klick auf den Doppelpfeil Doppelpfeil. Im Dialog entfernen Sie erst einmal das Häkchen bei (Alle Spalten auswählen) und auch bei Ursprünglichen Spaltennamen … Danach setzen Sie jeweils das Häkchen bei Produkt, M und bei Zähler. Auf den ersten Blick sieht das Ergebnis vielleicht etwas unkoordiniert aus:

Alle Monate in scheinbar ungeordneter Reihenfolge

Alle Monate in scheinbar ungeordneter Reihenfolge

Sie erinnern sich, in der Spalte Index steht ja eigentlich der numerische Wert des Monats. Darum werden sie als erstes diese Spalte aufsteigend sortieren. Und schon sind die Monate in der 1. Spalte nach ihrer logischen Reihenfolge sortiert. Warum dieser Umweg? Excel selbst ist zwar in der Lage, die Monatskürzel korrekt zu sortieren, Power Query ist dazu jedoch nicht fähig. – Die Spalten Index und M werden jetzt nicht mehr gebraucht, löschen Sie diese.

▲ nach oben …

Jetzt kommt die 100.000 € Frage: Welche Einträge sollen in der Kreuztabelle als Überschrift erscheinen? Diese Spalte markieren Sie durch einen Klick in die entsprechende Überschrift. Und ich denke, sie haben bei MMM geklickt. 🙂  Gut so, denn jetzt wechseln Sie zum Menü Transformieren und suchen in der Gruppe Beliebige Spalte das Symbol für Pivotieren (Achtung: nicht Entpivotieren):

Etwas versteckt: der Menüpunkt Pivotieren

Etwas versteckt: der Menüpunkt Pivotieren

Umgehend tut sich ein neuer Dialog auf, wo sie doch die eine oder andere Veränderung vornehmen müssen:

Hier werden sie einige Einstellungen verändern …

Gerade in solchen nicht so oft angewendeten Funktionalitäten schaue ich schon einmal genau hin und habe mir auch angewöhnt, Texte immer wieder genau durchzulesen, um zum gewünschten Ergebnis zu kommen. Hier steht unscheinbar aber ganz klar: Verwenden Sie die Namen in Spalte "MMM" zum Erstellen neuer Spalten. Also: Die Spalte mit dem Namen MMM hatten Sie ja bereits markiert und diese Inhalte werden nun als Überschrift für die Kreuztabelle verwendet. Die Bezeichnung Wertespalte ist vielleicht nicht ganz so eingängig, aber wenn sie einem Moment auf das (i) neben Wertespalte zeigen, wird ein erklärender Text eingeblendet.

In den reinen Datenbereich sollen ja jeweils die Werte der Spalte Zähler eingetragen werden, darum wählen Sie im Kombinationsfeld die Zeile mit Zähler aus. Nun noch ein Klick auf Erweiterte Optionen und es wird ein Kombinationsfeld für die Aggregatwertfunktion eingeblendet. Hier wählen Sie statt Summe die unterste Auswahl Nicht aggregieren. Nach einem Klick auf OK ist prinzipiell das Wunschergebnis schon erreicht:

Das sieht schon fast fertig aus. Allerdings stört noch etwas …

Das sieht schon fast fertig aus. Allerdings stört noch etwas …

Zunächst bin ich froh, dass die Abfrage und damit auch die künftige Tabelle ein vernünftiges Aussehen hat und auch in der Form einer Kreuztabelle vorliegt. Beim genauen hinsehen fällt mir aber auf, dass die Zeile 1 absolut leer ist. Meine erste Idee: Das ist die Zeile für Produkt6, wo ganz offensichtlich nichts verkauft worden ist. Als ich aber in den Roh-Daten nachgesehen habe merkte ich, dass es Produkt6 überhaupt nicht gibt. Also war das ein Holzweg. Wie auch immer, das muss korrigiert werden. Der vielleicht beste Weg: Start | Zeilen verringern | Zeilen entfernen | Erste Zeilen entfernen und im Dialog dann die 1 für die eine zu entfernende Zeile eintragen. Alternativ hätten sie natürlich auch die Spalte Produkt so filtern können, dass der Wert (NULL) demarkiert wird. Der Effekt ist der gleiche. Und das Ergebnis sieht doch wirklich gut aus:

Der Lohn der Mühe

Der Lohn der Mühe 😉

Ich empfinde es als übersichtlicher, wenn die derzeit leeren Zellen auch leer bleiben. Denn der Fragesteller hatte in seinem Beispiel in diese Zellen immer eine 0 eingefügt; das zu realisieren wäre aber auch kein Problem, einfach in der Abfrage lösen (siehe Teil 1 oder im Video).

▲ nach oben …

1. Teil Nur die Monate mit Umsätzen
2. Teil Alle Monate (mit Hilfs-Tabelle)
3. Teil Alle Monate, Monatsdarstellung nach Auswahl, ohne Hilfs-Tabelle

Reference: #8755

Dieser Beitrag wurde unter Datum & Zeit, Datum und Zeit, Foren-Q&A, Ohne Makro/VBA, Power Query, PQ-Formeln (Sprache M) abgelegt und mit , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Schreibe einen Kommentar