在Power Query中，以下代码适用于任意数量的类型/值列对。

• 取消透视除ID列以外的所有列
• 添加自定义列以定义取消透视的值是类型还是类型值
• 添加一个索引列，然后执行整数运算/除以2运算，以便按所需顺序排序
• 没有聚合的透视表，使用自定义函数作为“内置”函数将出现多项错误。
  自定义无聚合透视函数
• 按备注中的注解重新命名 *

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll 

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

常规查询

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"ID", type text}, {"Type 1", type text}, {"Type 1 Value", Int64.Type}, {"Type 2", type text}, {"Type 2 Value", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", 
        each if Text.EndsWith([Attribute],"Value") then "Value" else "Type"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute"}),
    
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),
    
    Pivot = fnPivotAll(#"Removed Columns1","Custom","Value"),
    
    #"Removed Columns2" = Table.RemoveColumns(Pivot,{"Integer-Division"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns2",{{"Type", type text}, {"Value", Int64.Type}})
in
    #"Changed Type1"

更一般地说，在powerquery中垂直堆叠，同时保留某些列

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
base_columns=1, groupsof=2, //stack them
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)),
#"Rename"=Table.RenameColumns(#"Added Custom",List.Zip({Table.ColumnNames(#"Added Custom"),List.FirstN(Table.ColumnNames(Source),base_columns+groupsof)}))
in #"Rename"

或者先横向堆叠，然后向下堆叠，这在较大的数据集上要慢得多

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "ResortIndex", 0, 1, Int64.Type),
base_columns=1, groupsof=2, //stack them
Combo = List.RemoveLastN(List.Transform(List.Split(List.Skip(Table.ColumnNames(#"Added Index"),base_columns),groupsof), each List.FirstN(Table.ColumnNames(#"Added Index"),base_columns) &{"ResortIndex"}& _),1),
#"Added Custom" =List.Accumulate(Combo, #table({"Column1"}, {}),(state,current)=> state & Table.Skip(Table.DemoteHeaders(Table.SelectColumns(#"Added Index", current)),1)),
#"Rename"=Table.RenameColumns(#"Added Custom",List.Zip({Table.ColumnNames(#"Added Custom"),List.FirstN(Table.ColumnNames(#"Added Index"),base_columns)&{"ResortIndex"}&List.Range(Table.ColumnNames(#"Added Index"),base_columns,groupsof)})),
#"Sorted Rows" = Table.Sort(Rename,{{"ResortIndex", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"ResortIndex"})
in  #"Removed Columns"

可能有一个更好的方法，但是如果您首先使用特定的唯一分隔符将4个列连接起来，以便稍后在自定义列中拆分，那么您可以在PQ中找到一个解决方法：

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type1", type text}, {"Type1 Val", Int64.Type}, {"Type2", type text}, {"Type2 Val", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom1", each [Type1]&"|"&Number.ToText([Type1 Val])&"$"&[Type2]&"|"&Number.ToText([Type2 Val])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Type1", "Type1 Val", "Type2", "Type2 Val"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Custom1", Splitter.SplitTextByDelimiter("$", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom1", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Custom1", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Custom1.1", "Custom1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom1.1", type text}, {"Custom1.2", Int64.Type}})
in
    #"Changed Type2"

以防您标记了"Excel-Formula"并且可以访问ms365：

H1中的公式：

=REDUCE({"ID","Type","Val"},ROW(A2:A5),LAMBDA(X,Y,VSTACK(X,INDEX(A:E,Y,{1,2,3}),INDEX(A:E,Y,{1,4,5}))))

或公式：
=SORT(VSTACK(A2:C5,HSTACK(A2:A5,D2:E5)))