Here is another generic function
Value.ReplaceType((table as table) =>
let
combineColumnName = Text.NewGuid()
in
Value.ReplaceType((primaryAttributeColumn as text, subAttributeColumns as list) as function =>
Value.ReplaceType((primaryAttributeValues as list) =>
let
primaryValueColumnNameFn = each primaryAttributeColumn & "." & Text.From(_),
primaryValueColumnNames = List.Transform(primaryAttributeValues, primaryValueColumnNameFn),
attributeColumnToText = Table.TransformColumns(table, {primaryAttributeColumn, primaryValueColumnNameFn, type text}),
mergeNewColumns = Table.CombineColumns(attributeColumnToText, subAttributeColumns, Value.ReplaceType(each Record.FromList(_, subAttributeColumns), type function(values as list) as Type.TableRow(Value.Type(Table.SelectColumns(#table(Value.Type(attributeColumnToText), {}), subAttributeColumns)))), combineColumnName),
pivot = Table.Pivot(mergeNewColumns, primaryValueColumnNames, primaryAttributeColumn, combineColumnName),
expandPivotColumn = List.Accumulate(primaryValueColumnNames, pivot, (t, c) => Table.ExpandRecordColumn(t, c, subAttributeColumns, List.Transform(subAttributeColumns, each c & "." & _)))
in
expandPivotColumn,
type function (primaryAttributeValues as (type {text} meta [Documentation.AllowedValues = List.Distinct(Table.Column(table, primaryAttributeColumn))])) as {text} meta [Documentation.Description = "Pivot Multiple (Step 3)", Documentation.LongDescription = "Step 3: Confirm the primary attribute values"]),
type function
(
primaryAttributeColumn as (type text meta [Documentation.AllowedValues = Table.ColumnNames(table)]),
subAttributeColumns as (type {text} meta [Documentation.AllowedValues = Table.ColumnNames(table)])
) as function meta [Documentation.Description = "Pivot Multiple (Step 2)", Documentation.LongDescription = "Step 2: Choose the primary attribute, and the list of sub attributes"]
),
(type function (table as table) as table) meta [Documentation.Description = "Pivot Multiple", Documentation.LongDescription = "Step 1: Choose a table to perform a multiple Pivot"]
)
