Search code examples
excelvbapivotpowerqueryunpivot

Convert table to reflect column names as row values

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?

Solution

  • It's fairly simple with Power Pivot:

    enter image description here

    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