Entpivotieren mit Power Query

Kurz gesagt: Markiere die Spalten, die stehen bleiben sollen (zum Beispiel Produkt), dann Transformieren > Andere Spalten entpivotieren. Aus den übrigen Überschriften wird die Spalte „Attribut“, aus ihren Werten die Spalte „Wert“. M: Table.UnpivotOtherColumns(Quelle, {"Produkt"}, "Attribut", "Wert").

Probier es gleich aus

Die Absatzzahlen stehen mit einer Spalte je Monat da. Bau daraus eine Liste mit einer Zeile je Produkt und Monat.

Aufgabe Die Absatzzahlen stehen in einer Kreuztabelle mit einer Spalte je Monat. Bring sie in Listenform: eine Zeile je Produkt und Monat.

	A	B	C	D
1	Produkt	Jan	Feb	Mär
2	Papier	120	135	110
3	Toner	80		95
4	Ordner	60	70	65
5	Stifte	40	45

Zwei Dinge fallen im Ergebnis auf: Die leeren Zellen sind verschwunden, und die Monatsnamen stehen jetzt als Werte in der Spalte Attribut. Beides ist gewollt. Leere Werte erzeugen beim Entpivotieren keine Zeile.

Warum eine Liste besser ist als eine Kreuztabelle

Eine Kreuztabelle hat für jeden Monat eine eigene Spalte. Kommt ein Monat dazu, ändert sich der Aufbau, und jede Formel, die bis Spalte D rechnet, muss angepasst werden. In der Liste kommt einfach eine Zeile dazu. Eine PivotTable kann dann Monate als Spalten, Produkte als Zeilen oder umgekehrt zeigen, und SUMMEWENNS summiert nach Produkt und Monat zugleich.

Vorher (Kreuztabelle)	Nachher (Liste)
Produkt | Jan | Feb | Mär	Produkt | Attribut | Wert
Papier | 120 | 135 | 110	Papier | Jan | 120 Papier | Feb | 135 Papier | Mär | 110

Drei Befehle zum Entpivotieren

• Andere Spalten entpivotieren: Du markierst die Spalten, die bleiben. Alles andere wird gedreht, auch Monatsspalten, die beim nächsten Export neu dazukommen. Meist die beste Wahl.
• Spalten entpivotieren: Du markierst die Spalten, die gedreht werden. Microsoft legt den Schritt dabei so an, dass er ebenfalls neue Spalten mitnimmt.
• Nur ausgewählte Spalten entpivotieren: Genau die markierten Spalten werden gedreht, neue Spalten bleiben außen vor.

Danach benennst du Attribut und Wert per Doppelklick sinnvoll um, etwa in Monat und Absatz.

Noch eine Übung: erst die Gesamtspalte weg

Berichte haben oft eine Summenspalte am Rand. Würdest du sie mit entpivotieren, stünde „Gesamt“ als eigenes Quartal in der Liste, und jede Summe darüber wäre doppelt so hoch.

Aufgabe Bring die Quartalszahlen in Listenform (eine Zeile je Region und Quartal). Die Spalte Gesamt gehört nicht in die Liste, sonst würde sie später doppelt gezählt.

	A	B	C	D	E	F
1	Region	Q1	Q2	Q3	Q4	Gesamt
2	Nord	310	280	330	360	1280
3	Süd	190	210	205	240	845
4	West	150	160	170	155	635

Diese Übung gehört zu Premium.

Mit Premium öffnest du alle Übungen jeder Lektion, übst unbegrenzt mit neuen Aufgaben und kannst den Abschlusstest mit Zertifikat machen.

Premium ansehen Schon Premium? Anmelden

Die erste Übung dieser Lektion ist kostenlos.

Wenn mehrere Spalten stehen bleiben

Ein Budgetbericht hat oft Kostenstelle, Konto und Bezeichnung links und zwölf Monatsspalten rechts. Dann markierst du die drei beschreibenden Spalten und entpivotierst die anderen. Jede Zeile der Liste trägt danach Kostenstelle, Konto, Bezeichnung, Monat und Betrag. Genau diese Form erwartet eine PivotTable, wenn du zum Beispiel Plan und Ist je Kostenstelle und Quartal vergleichen willst.

Der M-Code der zweiten Übung

= Table.RemoveColumns(Quelle,{"Gesamt"})
= Table.UnpivotOtherColumns(#"Entfernte Spalten", {"Region"}, "Attribut", "Wert")

In Table.UnpivotOtherColumns stehen nur die Spalten, die bleiben. Die Quartale werden nirgends genannt. Genau deshalb nimmt der Schritt ein neues Quartal beim nächsten Aktualisieren automatisch mit, ohne dass du etwas anpassen musst.

Typische Fehler

Summen sind doppelt so hoch

Eine Gesamt- oder Summenspalte wurde mit entpivotiert. Entferne sie vorher mit Spalten entfernen. Dasselbe gilt für eine Summenzeile am Ende des Berichts: Die filterst du vorher mit Zeilen filtern heraus.

Die falschen Spalten werden gedreht

Bei „Andere Spalten entpivotieren“ markierst du die Spalten, die bleiben sollen, nicht die Monate. Bleiben mehrere Spalten stehen, etwa Region und Produkt, markierst du beide.

Die Werte sind Text

Kamen die Zahlen als Text an, sind sie es auch nach dem Entpivotieren. Setz für die Spalte Wert den Datentyp auf Ganze Zahl oder Dezimalzahl.

Auf Englisch

Im englischen Excel heißen die Befehle Unpivot Other Columns, Unpivot Columns und Unpivot Only Selected Columns auf der Registerkarte Transform. Die neuen Spalten heißen dort Attribute und Value.

In welchen Excel-Versionen geht das?

Entpivotieren gibt es im Power Query-Editor von Excel 2016 und neuer für Windows und in Microsoft 365, auf dem Mac nur mit Microsoft 365. Ohne Power Query gibt es in Excel keinen einzelnen Befehl dafür. Mehr auf der Einstiegsseite zu Power Query.

Häufige Fragen