Spalten entpivotieren
In Power Query können Sie Spalten in Attributwertpaare umwandeln, wobei Spalten zu Zeilen werden.
Diagramm, das die linke Tabelle mit einer leeren Spalte und Zeilen und den Attributwerten A1, A2 und A3 als Spaltenüberschriften zeigt. In dieser Tabelle enthält die Spalte A1 die Werte V1, V4 und V7. Die Spalte A2 enthält die Werte V2, V5 und V8. Die Spalte A3 enthält die Werte V3, V6 und V9. Wenn die Spalten entpivotiert, enthält die rechte Tabelle des Diagramms eine leere Spalte und Zeilen, eine Attributspalte mit neun Zeilen, in denen A1, A2 und A3 dreimal wiederholt werden, und eine Wertespalte mit den Werten V1 bis V9.
Bei einer Tabelle wie der folgenden, bei der Länderzeilen und Datumsspalten eine Wertematrix erstellen, ist es schwierig, die Daten auf skalierbare Weise zu analysieren.
Screenshot einer Tabelle mit der Spalte „Land“, die im Datentyp „Text“ festgelegt ist, und drei Spalten mit den Daten 1. Juni 2023, 1. Juli 2023 und 1. August 2023, die als Datentyp „Ganze Zahl“ festgelegt sind. Die Spalte "Land" enthält USA in Zeile 1, Kanada in Zeile 2 und Panama in Zeile 3.
Stattdessen können Sie die Tabelle in eine Tabelle mit nicht pivotierten Spalten umwandeln, wie in der folgenden Abbildung dargestellt. In der transformierten Tabelle ist es einfacher, das Datum als Attribut zum Filtern zu verwenden.
Screenshot der Tabelle, die eine Spalte "Land" mit dem Datentyp "Text", eine Spalte "Attribut" mit dem Datentyp "Text" und eine Spalte "Wert" mit dem Datentyp "Ganze Zahl" enthält. Die Spalte „Land“ enthält USA in den ersten drei Zeilen, Kanada in den nächsten drei Zeilen und Panama in den letzten drei Zeilen. Die Spalte "Attribut" enthält das Datum vom 1. Juni 2023 in der ersten, vierten und siebten Zeile, das Datum vom 1. Juli 2023 in der zweiten, fünften und achten Zeile sowie das Datum vom 1. August 2023 in der dritten, sechsten und neunten Zeile.
Der Schlüssel in dieser Transformation besteht darin, dass Sie über eine Reihe von Datumsangaben in der Tabelle verfügen, die alle Teil einer einzelnen Spalte sein sollten. Der jeweilige Wert für jedes Datum und jedes Land sollte sich in einer anderen Spalte befinden, wodurch effektiv ein Attributwertpaar erstellt wird.
Power Query erstellt immer das Attribut-Wert-Paar mithilfe von zwei Spalten:
- Attribut: Der Name der Spaltenüberschriften, die entpivotiert wurden.
- Wert: Die Werte, die sich unter jeder der entpivotierten Spaltenüberschriften befanden.
Es gibt mehrere Stellen in der Benutzeroberfläche, an denen Sie Spalten entpivotieren finden können. Sie können mit der rechten Maustaste auf die Spalten klicken, die Sie entpivotieren möchten, oder Sie können den Befehl auf der Registerkarte Transformieren im Menüband auswählen.
Es gibt drei Möglichkeiten, Spalten in einer Tabelle zu entpivotieren:
- Spalten entpivotieren
- Andere Spalten entpivotieren
- Nur ausgewählte Spalten entpivotieren
Spalten entpivotieren
Für das zuvor beschriebenen Szenario müssen Sie zunächst die Spalten auswählen, die Sie entpivotieren möchten. Sie können STRG- auswählen, sodass Sie so viele Spalten auswählen können, wie Sie benötigen. In diesem Szenario möchten Sie alle Spalten mit Ausnahme der Spalten mit dem Namen Landauswählen. Nachdem Sie die Spalten ausgewählt haben, klicken Sie mit der rechten Maustaste auf eine der ausgewählten Spalten, und wählen Sie dann Spalten entpivotieren aus.
Screenshot: Tabelle mit den ausgewählten Spalten „1. Juni 2023“, „1. Juli 2023“ und „1. August 2023 „und dem Befehl „Spalten entpivotieren“ im Kontextmenü
Das Ergebnis dieses Vorgangs liefert das Ergebnis, das in der folgenden Abbildung dargestellt wird.
Besondere Überlegungen
Stellen Sie sich nach dem Erstellen der Abfrage aus den vorherigen Schritten vor, dass die erste Tabelle aktualisiert wird, um wie der folgende Screenshot zu aussehen.
Screenshot der Tabelle mit den gleichen ursprünglichen Datumsspalten "Land", 1. Juni 2023, 1. Juli 2023 und "August 2023" mit der Hinzufügung einer Datumsspalte vom 1. September 2023. Die Spalte "Land" enthält weiterhin die Werte USA, Kanada und Panama, hat aber auch Großbritannien zur vierten Zeile hinzugefügt und Mexiko zur fünften Zeile hinzugefügt.
Beachten Sie, dass Sie eine neue Spalte für das Datum 1. September 2023 (1.09.2023) und zwei neue Zeilen für die Länder/Regionen GROßBRITANNIEN und Mexiko hinzufügen.
Wenn Sie Ihre Abfrage aktualisieren, beachten Sie, dass der Vorgang in der aktualisierten Spalte abgeschlossen ist, sich aber nicht auf die Spalte auswirkt, die ursprünglich nicht ausgewählt wurde (Landin diesem Beispiel). Dies bedeutet, dass alle neuen Spalten, die Sie der Quelltabelle hinzugefügt haben, ebenfalls entpivotiert sind.
Die folgende Abbildung zeigt, wie Ihre Abfrage nach der Aktualisierung mit der neuen aktualisierten Quelltabelle aussieht.
Screenshot der Tabelle mit den Spalten "Land", "Attribut" und "Wert". Die ersten vier Zeilen der Spalte "Land" enthalten USA, die zweiten vier Zeilen enthalten Kanada, die dritten vier Zeilen enthalten Panama, die vierten vier Zeilen enthalten UK, und die fünften vier Zeilen enthalten Mexiko. Die Spalte "Attribut" enthält die Datumsangaben vom 1. Juni 2023, 1. Juli 2023 und August 2023 in den ersten vier Zeilen, die für jedes Land wiederholt werden.
Andere Spalten entpivotieren
Sie können auch die Spalten auswählen, die Sie nicht entpivotieren möchten, und die restlichen Spalten in der Tabelle entpivotieren. Bei diesem Vorgang kommt Andere Spalten entpivotieren ins Spiel.
Das Ergebnis dieses Vorgangs entspricht genau dem Ergebnis, das Sie mit Spalten entpivotieren erhalten haben.
Screenshot: Tabelle mit der Spalte „Land“, die als Datentyp „Text“ festgelegt ist, der Spalte „Attribut“, die als Datentyp „Text“ festgelegt ist, und der Spalte „Wert, die als Datentyp „Ganze Zahl“ festgelegt ist. Die Spalte Land enthält USA in den ersten drei Zeilen, Kanada in den nächsten drei Zeilen und Panama in den letzten drei Zeilen. Die Spalte "Attribut" enthält das Datum vom 1. Juni 2023 in der ersten, vierten und siebten Zeile, das Datum vom 1. Juli 2023 in der zweiten, fünften und achten Zeile sowie das Datum vom 1. August 2023 in der dritten, sechsten und neunten Zeile.
Anmerkung
Diese Transformation ist für Abfragen mit einer unbekannten Anzahl von Spalten von entscheidender Bedeutung. Der Vorgang entpivotiert alle Spalten Ihrer Tabelle mit Ausnahme der von Ihnen ausgewählten. Dies ist eine ideale Lösung, wenn die Datenquelle Ihres Szenarios bei einer Aktualisierung neue Datumsspalten erhalten hat, da diese übernommen und entpivotiert werden.
Besondere Überlegungen
Ähnlich wie beim Vorgang Spalten entpivotieren werden bei einer Aktualisierung Ihrer Abfrage und der Übernahme weiterer Daten aus der Datenquelle alle Spalten entpivotiert, mit Ausnahme der zuvor ausgewählten.
Um diesen Prozess zu veranschaulichen, sagen Sie, dass Sie eine neue Tabelle wie die in der folgenden Abbildung haben.
Screenshot der Tabelle mit den Spalten "Land", 1. Juni 2023, 1. Juli 2023, 1. August 2023 und 1. September 2023, wobei alle Spalten auf den Datentyp "Text" festgelegt sind. Die Spalte "Land" enthält von oben nach unten: USA, Kanada, Panama, Großbritannien und Mexiko.
Sie können die Spalte Land auswählen und dann Andere Spalte entpivotieren auswählen, was zu folgendem Ergebnis führt.
Screenshot der Tabelle mit den Spalten "Land", "Attribut" und "Wert". Die Spalten "Land" und "Attribut" werden auf den Datentyp "Text" festgelegt. Die Spalte "Wert" wird auf den Datentyp "Ganzer Wert" festgelegt. Die ersten vier Zeilen der Spalte "Land" enthalten USA, die zweiten vier Zeilen enthalten Kanada, die dritten vier Zeilen enthalten Panama, die vierten vier Zeilen enthalten UK, und die fünften vier Zeilen enthalten Mexiko. Die Spalte "Attribut" enthält den 1. Juni 2023, den 1. Juli 2023, den 1. August 2023 und den 1. September 2023 in den ersten vier Zeilen, die für jedes Land wiederholt werden.
Nur ausgewählte Spalten entpivotieren
Der Zweck dieser letzten Option besteht darin, nur bestimmte Spalten in Ihrer Tabelle zu entpivotieren. Diese Option ist für Szenarien wichtig, in denen Sie es mit einer unbekannten Anzahl von Spalten aus Ihrer Datenquelle zu tun haben und nur die ausgewählten Spalten entpivotieren möchten.
Um diesen Vorgang auszuführen, wählen Sie die zu entpivotierenden Spalten aus, in diesem Beispiel alle Spalten außer der Spalte Land. Klicken Sie dann mit der rechten Maustaste auf eine der ausgewählten Spalten und wählen Sie Nur ausgewählte Spalten entpivotieren aus.
Beachten Sie, dass dieser Vorgang die gleiche Ausgabe wie die vorherigen Beispiele erzeugt.
Screenshot der Tabelle, die eine Spalte "Land" mit dem Datentyp "Text", eine Spalte "Attribut" mit dem Datentyp "Text" und eine Spalte "Wert" mit dem Datentyp "Ganze Zahl" aufweist. Die Spalte "Land" enthält USA in den ersten drei Zeilen, Kanada in den nächsten drei Zeilen und Panama in den letzten drei Zeilen. Die Spalte "Attribut" enthält das Datum vom 1. Juni 2023 in den ersten, vierten und siebten Zeilen, das Datum vom 1. Juli 2023 in der zweiten, fünften und achten Zeile sowie das Datum vom 1. August 2023 in der dritten, sechsten und neunten Zeile.
Besondere Überlegungen
Wenn sich unsere Quelltabelle nach einer Aktualisierung ändert und eine neue Spalte mit dem Datum 1.9.2020 sowie neue Zeilen für das Vereinigte Königreich und Mexiko enthält, unterscheidet sich die Ausgabe der Abfrage von den vorherigen Beispielen. Nehmen wir an, dass sich unsere Quelltabelle nach einer Aktualisierung in die Tabelle in der folgenden Abbildung ändert.
Die Ausgabe unserer Abfrage sieht wie in der folgenden Abbildung aus.
Dies sieht so aus, weil der Unpivot-Vorgang nur auf die 6/1/2020, 7/1/2020und 8/1/2020 Spalten angewendet wurde, sodass die Spalte mit der Überschrift 9/1/2020 unverändert bleibt.