Search code examples
excelpowerquery

I need to transpose and merge multiple columns

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:

Deal 1ST Quarter 2ND Quarter 3RD Quarter Value 1ST Quarter Value 2ND Quarter Value 3RD Quarter
A Q2 Q3 Null $100 $200 Null
B Q1 Q2 Q3 $50 $30 $40

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

Deal Quarter Value
A Q2 $100
A Q3 $100
A Null Null
B Q1 $50
B Q2 $30
B Q3 $40

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

Solution

  • 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.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"

    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
    enter image description here

    Results
    enter image description here

    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"