Column order in PowerView

Question

I have following sample data in PowerPivot:

id  name    type    color  
1   aaa     x       blue  
2   bbb     y       red  
3   ccc     x       blue 
4   ddd     y       orange  
5   eee     z       black

When I create pivot table in Excel and enable show details (doubleclick on specific value), I get follwing output:

[$Sheet1].[id] [$Sheet1].[name] [$Sheet1].[type] [$Sheet1].[color]
1 aaa x blue
2 bbb y red
3 ccc x blue
4 ddd y orange
5 eee z black

What I'd like to set is my own column order or specify columns which I want to see. E.g.:

[$Sheet1].[id] [$Sheet1].[color]
1 blue
2 red
3 blue
4 orange
5 black

Could you please advise? Thanks

Answer 1

You need to merge the sort table with the main table, so the sorting numbers are inside the main table. Then you can click the Colour column and set the sort order to the sort column using the "Sort by Column" command on the Home ribbon of the Power Pivot window. In the screenshot, I have added the desired sort numbers

1 - red
2 - blue
3 - black
4 - orange

and added the values in a new column. Then defined the Sort by Column and created a pivot table where the color data is in the rows. Note how the desired sort order is applied correctly.

There are many different ways how you can get the sort numbers into the helper sort column.