Excel Unpivot Multiple Columns to a Single Column

Question

What I want to achieve:

what i wanted to achieve:

As the title says, is there any way to convert my table structure? I've tried using Power Query but it didn't work. Any kind of help is greatly appreciated. Thanks!

So, I was trying to pivot type and type value, but it seems impossible if I maintain the current table structure since it would cause duplicates when I wanted to aggregate on type.

Should I remake the table structure or there is any way to get around this problem?

Thanks in advance!

Ron Rosenfeld · Accepted Answer · 2023-02-07 12:25:02Z

3

In Power Query, the following is adaptable to any number of type/value column pairs.

Unpivot all except the ID column
Add a custom column to define if the unpivoted value is a Type or a Type Value
Add an Index column and then do an integer/divide by 2 so things will sort in the desired order
Pivot with no aggregation, using a custom function as the "built-in" function will error with multiple items.

Custom function to Pivot with No aggregation
Rename as noted in comments

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

Regular Query

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"

answered Feb 7, 2023 at 12:25

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

horseyride Over a year ago

Ok, I was curious, since we've done this before on other threads, so I decided to test both methods. Three leading columns and 78 data columns that are to be sorted into groups of 3, using 1.2 million data rows from a CSV file. Table.Rowcount for List.Accumulate method 4m20s and 4min40s. Table.Rowcount for Pivot with No aggregation method was more than 9m in both tries before I gave up. No Table.Buffer() for either

Ron Rosenfeld Over a year ago

@horseyride Thank you for running that test. When I started using this, it was faster than using the Table.Group method, and I did not compare to other methods. However, your code does not return the output in the same order as indicated in the OP's post. He shows that he is stacking each individual row into a column, then stacking those stacks. Your code results in a stacking of the columns, one under the other.

horseyride Over a year ago

Agreed, I am not preserving order. Hadn't thought of that. Could just add an initial index as another leading column and resort when done, but point taken. Thanks

Ron Rosenfeld Over a year ago

@horseyride Sorting a million+ row table can take a while, though. One of these days I should generate a long CSV and test a few methods.

horseyride Over a year ago

I just set it up, and when I get a minute, will report back. I am using 78x1.2M table + 3 columns + the index now I guess. Its at least 2x longer then my other method but I had real stuff to do

|

horseyride · Accepted Answer · 2023-02-13 22:20:04Z

More generically to stack vertically in powerquery while keeping certain columns

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"

What seems to be fastest method of those I've tested

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
leading=1, groupsof=2,
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.Split( List.RemoveFirstN(Record.ToList( _),leading), groupsof) ),
#"Added Custom0" = Table.AddColumn(#"Added Custom", "Custom0", each Text.Combine(List.FirstN(Record.ToList(_),leading),"|")),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom0",{"Custom0", "Custom"}),
#"Expanded Custom" = Table.ExpandListColumn( #"Removed Other Columns", "Custom"),
#"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
#"Merged Columns" = Table.CombineColumns(#"Extracted Values",{"Custom0", "Custom"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Custom"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.FirstN(Table.ColumnNames(Source),leading+groupsof))
in #"Split Column by Delimiter"

JvdV · Accepted Answer · 2023-02-07 07:49:40Z

There is probably a better way, but if you first concat the 4 columns with specific unique delimiter to split on later in a custom column, you have a work-around in 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"

Just in case you tagged 'Excel-Formula' and you have access to ms365:

Formula in 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}))))

P.b · Accepted Answer · 2023-02-07 07:50:59Z

1

Or formula:

=SORT(VSTACK(A2:C5,HSTACK(A2:A5,D2:E5)))

answered Feb 7, 2023 at 7:50

P.b

12k2 gold badges15 silver badges34 bronze badges

Collectives™ on Stack Overflow

Excel Unpivot Multiple Columns to a Single Column

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related