Unpivoting data in google sheet

Question

I have the following data in Excel:

How do I unpivot the above into the following in Excel or Google Sheets?

Answer 1

in excel, bring the data into powerquery with data ... from table/range.... [x] headers

go into home advanced editor and use

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], //replace this row with the autogenerated row excel provides
base_columns=1, groupsof=3, 
Combo = List.Transform(List.Split(List.Skip(Table.ColumnNames(Source),base_columns),groupsof), each List.FirstN(Table.ColumnNames(Source),base_columns) & _),
#"Added Custom" =List.Accumulate(Combo, #table({"Column1"}, {}),(state,current)=> state & Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source, current)),1))
in #"Added Custom"

file .. close and load ... as table or pivot back to excel

Alternate for preserving row sort

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], //replace this row with the autogenerated row excel provides
#"Changed Type" =  Table.TransformColumnTypes(Source,List.Transform(Table.ColumnNames(Source), each {_, type text})),
leading=1, groupsof=3,
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Split( List.RemoveFirstN(Record.ToList( _),leading), groupsof) ),
#"Added Custom0" = Table.AddColumn(#"Added Custom", "Custom0", each Text.Combine(List.FirstN(Record.ToList(_),leading),"|")),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom0",{"Custom0", "Custom"}),
#"Expanded Custom" = Table.ExpandListColumn( #"Removed Other Columns", "Custom"),
#"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
#"Merged Columns" = if leading=0 then Table.RemoveColumns(#"Extracted Values",{"Custom0"}) else Table.CombineColumns(#"Extracted Values",{"Custom0", "Custom"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Custom"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.FirstN(Table.ColumnNames(#"Changed Type"),leading+groupsof))
in #"Split Column by Delimiter"