I have brand rankings (row values) by customer in the following table:
CustID | Brand1 | Brand2 | Brand3 | Brand4 | Brand5 | Brand6 | Brand10 |
---|---|---|---|---|---|---|---|
1 | 3 | 1 | 2 | 5 | 4 | ||
2 | 2 | 1 | 4 | 3 | 5 | ||
3 | 2 | 3 | 4 | 1 | 5 |
I would like to transform this table to represent rankings as columns and brands as rows:
CustID | 1 | 2 | 3 | 4 | 5 |
---|---|---|---|---|---|
1 | Brand2 | Brand3 | Brand1 | Brand10 | Brand5 |
2 | Brand3 | Brand2 | Brand6 | Brand5 | Brand10 |
3 | Brand6 | Brand1 | Brand2 | Brand4 | Brand10 |
I have 116 customers in total, 10 Brands and Rankings 1 to 5. I tried unpivoting and pivoting tables but values in pivot tables show counts, not Brand names.
How can I transform my table?
It's fairly simple with Power Pivot:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
UnpivotedData = Table.UnpivotOtherColumns(Source, {"CustID"}, "Brand", "Rank"),
FilteredData = Table.SelectRows(UnpivotedData, each [Rank] <> null),
ChangeType = Table.TransformColumnTypes(FilteredData,{{"Rank", type text}}),
PivotedData = Table.Pivot(ChangeType, List.Distinct(ChangeType[Rank]), "Rank", "Brand"),
SortedColumnNames = List.Sort(List.RemoveFirstN(Table.ColumnNames(PivotedData), 1), (x) => Number.FromText(x)),
SortedColumns = Table.ReorderColumns(PivotedData, List.Combine({{"CustID"}, SortedColumnNames}))
in
SortedColumns