Preciso transpor e mesclar várias colunas

Outra abordagem, possivelmente mais eficiente, é

• Despivotar
• Transforme a Attributecoluna para mostrar apenas QuartereValue
• Então Pivot sem agregação.

O problema com o último passo, usando a operação Pivot usual no PQ, é que isso resultará em um erro. No entanto, essa função personalizada evita esse problema.

Cole o código abaixo em uma consulta em branco no Editor avançado e renomeie-a como fnPivotNoAggregation
Função personalizada

//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"

Em seguida, use o seguinte para o código principal:
Código principal

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

Dados originais

Resultados

Nota: É possível fazer isso sem uma função personalizada, mas o tempo de execução seria um pouco maior.
Você certamente pode testar ambos e verificar qual é mais eficiente com seus dados reais

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"

Você pode fazer isso com uma combinação de agrupamento e desarticulação:

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