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.

Answer 1

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"