Transform data from columns to rows in excel

Question

I need to transform data that is in multiple rows and multiple columns into unique rows, but there are specific rules around what i need. An example of the current data format is below:

The split should be based on the style, colour and unique upc but i need to copy some of the fields to each unique upc for the style and colour. I also need to show a parent child relationship.

The example below is how I want the data to be shown.

I've tried doing this in power query...but totally stuck!

Thanks in advance for any advice.

Search this site for excel pivot. This sort of question has been asked and answered here before. — Ken White
– Ken White, Commented May 3, 2021 at 21:05
So it seems the example in my original post was just an example export from the source system and the actual data that needs to be transformed is a bit more complex. I've tried replicating the methods in the answers on the new source template but not having much luck...I'll keep trying with the methods below! Thanks! — Stuart Hart
– Stuart Hart, Commented May 4, 2021 at 19:10

horseyride · Accepted Answer · 2021-05-03 22:19:00Z

This seems to work in powerquery pasted into home...advanced editor.. assuming your initial table is range Table1 and similar to the sample structure you provided

Unpivot, remove numbers from the the attributes, group and add index for the later pivot, pivot. The rest is just custom columns and filling

 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"style", "colour"}, "Attribute", "Value"),
 #"removed numbers" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each Text.Remove(_, List.Transform({48..57}, each Character.FromNumber(_))), type text}}),
 #"Grouped Rows" = Table.Group(#"removed numbers", {"style", "colour", "Attribute"}, {{"data", each Table.AddIndexColumn(_, "Index", 2, 1), type table}}),
 #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Value", "Index"}, {"Value", "Index"}),
 #"Added Custom" = Table.AddColumn(#"Expanded data", "Custom", each if [Attribute]="description" then 1 else [Index]),
 #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
 #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value"),
 #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "parent", each if [description]=null then "child" else "parent"),
 #"Filled Down" = Table.FillDown(#"Added Custom1",{"description"}),
 #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Custom"})
 in  #"Removed Columns1"

Alternate version that uses index and transform on that column prior to pivoting

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"style", "color", "desc"}, "Attribute", "Value"),
#"removed numbers" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each Text.Remove(_, List.Transform({48..57}, each Character.FromNumber(_))), type text}}),
#"Added Index" = Table.AddIndexColumn(#"removed numbers", "Index", 1, .5),
#"Rounded Down" = Table.TransformColumns(#"Added Index",{{"Index", Number.RoundDown, Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Rounded Down", List.Distinct(#"Rounded Down"[Attribute]), "Attribute", "Value"),
#"Added Custom1" = Table.AddColumn(#"Pivoted Column", "parent", each "child"),

#"Add parent" = Table.Combine({Table.AddColumn(Table.Distinct(Table.SelectColumns(#"Added Custom1",{"style", "color", "desc"})), "parent", each "parent"), #"Added Custom1"}),

#"Removed Columns" = Table.RemoveColumns(#"Add parent",{"Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"style", "color", "upc", "size", "desc", "parent"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"style", Order.Ascending}, {"color", Order.Ascending}, {"upc", Order.Ascending}})
in  #"Sorted Rows"

Ron Rosenfeld · Accepted Answer · 2021-05-04 15:38:27Z

Here is another Power Query method that uses a custom function to enable creation of a Pivot Table with no aggregation where there are multiple items.

Examine the comments in the M-Code and the Applied steps, and also the reference in the custom function, to understand how it works:

To enter the custom function, select to

New Query => Blank Query.
Rename the Query from (probably) Query1 to fnPivotAll

M Code

//Rename Table3 to your actual table name
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

//Unpivot all except the style and color columns
 #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"style", "colour"}, "Attribute", "Value"),

//remove digits from the UPC and SIZE attributes
 remDigits = Table.TransformColumns(#"Unpivoted Other Columns",{
     {"Attribute", each Text.Remove(_, List.Transform({48..57}, each Character.FromNumber(_))), type text}}),

//Pivot on Attribute Column
//Custom function to use when there are multiple values for the column
pivot = fnPivotAll(remDigits,"Attribute","Value"),

//Fill in the blank descriptions
    #"Filled Down" = Table.FillDown(pivot,{"description"}),

//Group (by style, colour and description) to add a description row to each grouped table
    #"Grouped Rows" = Table.Group(#"Filled Down", {"style", "colour", "description"}, {
        {"All", each _, type table [style=text, colour=text, upc=number, size=any, description=text]},
        {"addRow", each Table.InsertRows(_, 0, {[style=[style]{0}, colour=[colour]{0}, upc=null, size=null, description=[description]{0}]})}       
        }),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"style", "colour", "description", "All"}),

//expand the grouped table
    #"Expanded addRow" = Table.ExpandTableColumn(#"Removed Columns", "addRow", {"style", "colour", "upc", "size", "description"}, {"style", "colour", "upc", "size", "description"}),

//Add column for Parent or child
    #"Added Custom" = Table.AddColumn(#"Expanded addRow", "Parent", each if [upc] = null then "Parent" else "Child")
in
    #"Added Custom"

Custom Function
named fnPivotAll -- Rename the Query

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

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

Collectives™ on Stack Overflow

Transform data from columns to rows in excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related