Lezen in het Engels

Delen via


Table.Pivot

Syntaxis

Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as table

Over

Gegeven een paar kolommen die kenmerk-waardeparen voorstellen, worden de gegevens in de kenmerkkolom tot kolomkoppen geroteerd.

Voorbeeld 1

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]
})

Voorbeeld 2

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]
})