我需要转置和合并多个列

另一种可能更有效的方法是

• 逆透视
• 转换Attribute列以仅显示Quarter和Value
• 然后在不进行聚合的情况下进行 Pivot。

后一步的问题在于，使用 PQ 中常见的 Pivot 操作会导致错误。但是，此自定义函数避免了该问题。

将以下代码粘贴到高级编辑器中的空白查询中，并将其重命名为fnPivotNoAggregation
自定义函数

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

//Rename:  fnPivotNoAggregation

(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
    
//Change next line to reflect actual Table name
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Deal", type text}, {"1ST Quarter", type text}, {"2ND Quarter", type text}, 
        {"3RD Quarter", type text}, {"Value 1ST Quarter", Int64.Type}, 
        {"Value 2ND Quarter", Int64.Type}, {"Value 3RD Quarter", type any}}),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Deal"}, "Attribute", "Values"),

    Normalize = Table.TransformColumns(#"Unpivoted Other Columns",
        {"Attribute", each let first = Text.BeforeDelimiter(_," ") in if first <> "Value" then "Quarter" else "Value"}),
    
    Pivot = fnPivotNoAggregation(Normalize,"Attribute","Values")
in
    Pivot

原始数据

结果

注意：不使用自定义函数也可以做到这一点，但执行时间会更长一些。
你当然可以测试两者，并确定哪种方法更适合你的实际数据

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Deal", type text}, {"1ST Quarter", type text}, {"2ND Quarter", type text},
        {"3RD Quarter", type text}, {"Value 1ST Quarter", Int64.Type}, {"Value 2ND Quarter", Int64.Type}, {"Value 3RD Quarter", type any}}),
        
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Deal"}, "Attribute", "Value"),
    
    Normalize = Table.TransformColumns(#"Unpivoted Other Columns",
        {"Attribute", each let first = Text.BeforeDelimiter(_," ") in if first <> "Value" then "Quarter" else "Value"}),
    
    #"Grouped Rows" = Table.Group(Normalize, {"Deal"}, {
        {"QV", (t)=>
            Table.FromColumns(
                {Table.SelectRows(t, each [Attribute]= "Quarter")[Value],
                 Table.SelectRows(t, each [Attribute]="Value")[Value]}, {"Quarter","Value"}
            ), type table[Quarter=text, Value=Currency.Type]}}),
   
    #"Expanded QV" = Table.ExpandTableColumn(#"Grouped Rows", "QV", {"Quarter", "Value"}, {"Quarter", "Value"})
in
    #"Expanded QV"

您可以通过结合使用逆透视和分组来实现：

let
    // Load data from the current workbook
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

    // Change column types for consistency
    ChangedTypes = Table.TransformColumnTypes(Source, {
        {"Deal", type text}, 
        {"1ST Quarter", type text}, 
        {"2ND Quarter", type text}, 
        {"3RD Quarter", type text}, 
        {"Value 1ST Quarter", Int64.Type}, 
        {"Value 2ND Quarter", Int64.Type}, 
        {"Value 3RD Quarter", type any}
    }),

    // Unpivot all columns except "Deal" to create a normalized structure
    UnpivotedTable = Table.UnpivotOtherColumns(ChangedTypes, {"Deal"}, "Attribute", "Value"),

    // Clean up the "Attribute" column by removing the "Value " prefix
    CleanedAttributes = Table.ReplaceValue(UnpivotedTable, "Value ", "", Replacer.ReplaceText, {"Attribute"}),

    // Group by "Deal" and "Attribute", collecting all corresponding values into a list
    GroupedTable = Table.Group(CleanedAttributes, {"Deal", "Attribute"}, {{"CollectedValues", each _[Value], type list}}),

    // Extract the first value from the list (assumed to be the period value)
    ExtractedValue1 = Table.AddColumn(GroupedTable, "Value1", each if List.Count([CollectedValues]) > 0 then [CollectedValues]{0} else null),

    // Extract the second value from the list (assumed to be the numeric value)
    ExtractedValue2 = Table.AddColumn(ExtractedValue1, "Value2", each if List.Count([CollectedValues]) > 1 then [CollectedValues]{1} else null),

    // Remove the original collected values list for clarity
    FinalTable = Table.RemoveColumns(ExtractedValue2, {"CollectedValues", "Attribute"})

in
    FinalTable