Table.Pivot
Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as table
Gegeven een paar kolommen die kenmerk-waardeparen voorstellen, worden de gegevens in de kenmerkkolom tot kolomkoppen geroteerd.
Neem de waarden 'a', 'b' en 'c' in de kenmerkkolom van de tabel ({ [ key = "x", attribute = "a", value = 1 ], [ key = "x", attribute = "c", value = 3 ], [ key = "y", attribute = "a", value = 2 ], [ key = "y", attribute = "b", value = 4 ] })
en draai ze in hun eigen kolom.
Gebruik
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"
)
uitvoer
Table.FromRecords({
[key = "x", a = 1, b = null, c = 3],
[key = "y", a = 2, b = 4, c = null]
})
Neem de waarden 'a', 'b' en 'c' in de kenmerkkolom van de tabel ({ [ 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 ] })
en draai ze in hun eigen kolom. Aan het kenmerk 'c' voor sleutel 'x' zijn meerdere waarden gekoppeld, dus gebruik de functie List.Max om het conflict op te lossen.
Gebruik
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
)
uitvoer
Table.FromRecords({
[key = "x", a = 1, b = null, c = 5],
[key = "y", a = 2, b = 4, c = null]
})