Table.Pivot
When provided with a table and attribute column containing pivot values, the program will generate new columns for each pivot value and populate them with corresponding values from the value column. I...
Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as tableWhen provided with a table and attribute column containing pivot values, the program will generate new columns for each pivot value and populate them with corresponding values from the value column. If needed, users can specify an aggregation function to manage duplicate key values in the attribute column.
In the table ({ [ key = "x", attribute = "a", value = 1 ], [ key = "x", attribute = "c", value = 3 ], [ key = "y", attribute = "a", value = 2 ], [ key = "y", attribute = "b", value = 4 ] }), the values "a", "b", and "c" in the attribute column are pivoted into their own column. To do this, use Power Query MTable.Pivot function with the following parameters: the table data, columns to pivot ("a", "b", "c"), attribute column, value column. The output will be a new table with pivoted columns for attributes "a", "b", and "c.
In the second example, the same operation is performed on a table with additional values for attribute "c" for key "x". Since there are multiple values associated with attribute "c", the function List.Max is used to resolve the conflict. The Power Query MTable.Pivot function is applied with the same parameters as before, including List.Max function. The resulting table will show the pivoted values for attributes "a", "b", and "c" with the resolved conflict for attribute "c" in the table out