CODEWITHSUNDEEP
excelpivot-tablepowerquery
Jacob Roy
Jacob Roy

Reputation: 3

Pivot columns with multiple instance (rows) of attribute

I've searched far and wide and haven't found an answer to this specific case, and wasn't able to adapt some of these solutions.

First of all, my data is a long list of attributes and their values for every product, structured like this: enter image description here

Structured Initial Data

Note that some products have a single value per attributes, but (and here's my problem) some products have different values for the same attribute.

When I pivot the table in PowerQuery, i get errors where the products have multiple instances of the same attributes.

The resulting table that i'm looking for would be structured like this: enter image description here

Structured Final Data

Thank you for your help!

Upvotes: 0

Views: 214

Answers (1)

horseyride
horseyride

Reputation: 21428

See if this works for you

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Sorted Rows" = Table.Sort(Source,{{"Products", Order.Ascending}, {"Attributes", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Products"}, {{"data", each _, type table}}),
#"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1),
#"Expanded data" = Table.ExpandTableColumn(#"Added Index1", "data", {"Attributes", "Values"}, {"Attributes", "Values"}),  
mGroup = Table.Group(#"Expanded data" , {"Attributes","Products"}, {{"GRP", each Table.AddIndexColumn(_, "Index2", 1, 1), type table}}),
#"Expanded GRP" = Table.ExpandTableColumn(mGroup, "GRP", {"Values", "Index", "Index2"}, {"Values", "Index", "Index2"}),
#"Added Custom" = Table.AddColumn(#"Expanded GRP", "Row#", each [Index]+[Index2]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index2"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attributes]), "Attributes", "Values"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Row#"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Products", "Each", "Pack"})
in #"Reordered Columns"

It groups on product and adds an index. Then it groups on product and Attribute and adds another index. The sum of those two are a unique row number you can use for pivoting

Upvotes: 1

Related Questions