Table.Unpivot
The process involves converting a group of columns in a table into pairs of attributes and values, which are then merged with the remaining values in each row.
Table.Unpivot(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as tableThe process involves converting a group of columns in a table into pairs of attributes and values, which are then merged with the remaining values in each row.
Transform the columns labeled "a", "b", and "c" in the given table ({[ key = "x", a = 1, b = null, c = 3 ], [ key = "y", a = 2, b = 4, c = null ]}) into pairs of attributes and values, a process known as unpivoting. This can be achieved using the Power Query M language with the MTable.Unpivot function as shown below:
MTable.Unpivot(
Table.FromRecords({
[key = "x", a = 1, b = null, c = 3],
[key = "y", a = 2, b = 4, c = null]
}),
{"a", "b", "c"},
"attribute",
"value"
)
The resulting table will show the data transformed into attribute-value pairs:
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]
})