Because the way a form is set up, every time a user fills out the form, different quarter information is stored in a single row. For data analysis purposes and simplicity, I would like to "transpose and merge" the columns with the same information. In this case all the columns containing **quarter **information and **value **information.

I have the following table:

Because the way a form is set up, every time a user fills out the form, different quarter information is stored in a single row. For data analysis purposes and simplicity, I would like to "transpose and merge" the columns with the same information. In this case all the columns containing **quarter **information and **value **information.

I have the following table:

I want to transpose the Quarter columns and their respective values and output the following:

I have tried transposing the columns but end up making a mess and multiple rows.

• excel
• powerquery

2 Answers 2

Another approach, possibly more efficient, is to

• Unpivot
• Transform the Attribute column to show only Quarter and Value
• Then Pivot with no aggregation.

The problem with the latter step, using the usual Pivot operation in PQ, is that it will result in an error. However, this custom function avoids that issue.

Paste the code below into a blank query in the Advanced Editor and rename it fnPivotNoAggregation
Custom Function

//credit: Cam Wallace  https://www.dingbatdata/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"

Then use the following for the Main code:
Main Code

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

Original Data

Results

Note: It is possible to do this without a custom function, but the execution time would be a bit longer.
You can certainly test both and ascertain which is more efficient with your actual data

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"

You can do it with a combination of unpivoting and grouping:

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