Table.Pivot
Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as table
Wandelt bei Angabe eines Spaltenpaars, das Attribut-Wert-Paare darstellt, die Daten in der Attributspalte in Spaltenüberschriften um.
Nimmt die Werte "a", "b" und "c" in der Attributspalte der Tabelle ({ [ key = "x", attribute = "a", value = 1 ], [ key = "x", attribute = "c", value = 3 ], [ key = "y", attribute = "a", value = 2 ], [ key = "y", attribute = "b", value = 4 ] })
und pivotiert sie in ihre eigene Spalte.
Verwendung
Table.Pivot(
Table.FromRecords({
[key = "x", attribute = "a", value = 1],
[key = "x", attribute = "c", value = 3],
[key = "y", attribute = "a", value = 2],
[key = "y", attribute = "b", value = 4]
}),
{"a", "b", "c"},
"attribute",
"value"
)
Ausgabe
Table.FromRecords({
[key = "x", a = 1, b = null, c = 3],
[key = "y", a = 2, b = 4, c = null]
})
Nimmt die Werte "a", "b" und "c" in der Attributspalte der Tabelle ({ [ key = "x", attribute = "a", value = 1 ], [ key = "x", attribute = "c", value = 3 ], [ key = "x", attribute = "c", value = 5 ], [ key = "y", attribute = "a", value = 2 ], [ key = "y", attribute = "b", value = 4 ] })
und pivotiert sie in ihre eigene Spalte. Dem Attribut "c" für den Schlüssel "x" sind mehrere Werte zugeordnet, verwenden Sie daher die Funktion List.Max, um den Konflikt zu beheben.
Verwendung
Table.Pivot(
Table.FromRecords({
[key = "x", attribute = "a", value = 1],
[key = "x", attribute = "c", value = 3],
[key = "x", attribute = "c", value = 5],
[key = "y", attribute = "a", value = 2],
[key = "y", attribute = "b", value = 4]
}),
{"a", "b", "c"},
"attribute",
"value",
List.Max
)
Ausgabe
Table.FromRecords({
[key = "x", a = 1, b = null, c = 5],
[key = "y", a = 2, b = 4, c = null]
})