How to pivot Multiple Columns

Thanks very much that worked!

Your solution is amazing, it works wonderful and is easy to follow.  Thank you for sharing.

Thank you for sharing this! For my use for a Word mail merge, I added steps to dynamically use the largest Table to expand the Grouped Rows. This enables using this even if the Pivot value isn't present for each group. Basically, I

1. Added a Row count aggregate column to the "Grouped Rows" action, alongside the Transpose column you created
2. Added an Index column
3. Calculated the Maximum count to determine which table had the MOST rows (that become columns later)
4. Filtered the Table to show only Rows with a [Count] equal to the max
5. Removed everything except for the first Row
6. Drilled down to the Index value of this record
7. Used THIS INDEX as the key for getting the Column Names
8. (kept everything after the same)

These steps could certainly be consolidated and shortened to quickly return the Index of the table with ALL columns present.

Here's the code:

 ...

#"Grouped Rows" = Table.Group(#"Merged Columns", {"ID"}, {
{"Transpose", (t)=>
let 
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(t, {"ID", "Milestone"}, "Attribute", "Value"),
#"Inserted Merged Column" = 
Table.AddColumn(
#"Unpivoted Other Columns", "Merged", each Text.Combine({[Milestone], [Attribute]}, ""), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Milestone", "Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Merged", "Value"}),
#"Transposed Table" = Table.Transpose(#"Reordered Columns"),
#"Removed Top Rows" = Table.Skip(#"Transposed Table",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in #"Promoted Headers"},
{"Count", each Table.RowCount(_), Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
#"Calculated Maximum" = List.Max(#"Added Index"[Count]),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Count] = #"Calculated Maximum")),
#"Kept First Rows" = Table.FirstN(#"Filtered Rows",1),
IndexOfMax = #"Kept First Rows"{[Count=#"Calculated Maximum"]}[Index],
#"Column Names" = Table.ColumnNames(#"Grouped Rows"[Transpose]{IndexOfMax})

...