Excel Pivot Multiple Columns

[Africat]

ItemList	Colour1	Colour2	Colour3	Colour4	Colour5	Colour6	Colour7	Colour8
Item 1	Blue	Yellow	Purple	Green	Orange	Red	Cyan	White
Item 2	Black	Magenta	Cyan	Yellow

<tbody>
</tbody>

Hi I'm new here so sorry about the unconventional formatting. I have a item table with different coloured paints. I have created a unique list of colours and I am trying to create a relationship between the UniqueColourList and all 8 colours cloumns on the item table so that when expanded shows only the item numbers that has that colour. I've gotten it to "work" by creating a relationship with all 8 colours (some sorta crazy 1-to-many x 8).
Practical use would be to expand the pivot table at blue and see all the Items that require blue so that forecasting can be done on how much blue paint to order & is require for exisitng orders etc.

I was wondering if there is some better method to get a cleaner result without powerpivot and with powerpivot?
Thanks

UniqueColourList

Blue

Black

Yellow

....

<tbody>
</tbody>

 

[Ron Coderre]

Here are two possibilities....
Using Power Query (Get & Transform):
I named the data range: rngColors
- Select the range and use: Data.From_Table (The Power Query editor will open)
- Promote the first row to headers
- Select the first column and use Unpivot Other Columns
- Remove the first two columns (ItemList, Attribute)
- Remove duplicates

These are the M-language steps:

let
    Source = Excel.CurrentWorkbook(){[Name="rngColors"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ItemList", type text}, {"Colour1", type text}, {"Colour2", type text}, {"Colour3", type text}, {"Colour4", type text}, {"Colour5", type text}, {"Colour6", type text}, {"Colour7", type text}, {"Colour8", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ItemList"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"ItemList", "Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

This is a manual approach:
- Use this Excel 2003 keyboard shortcut: ALT+DPP (to open a create Pivot Table window)
- Check: Multiple Consolidation Ranges.....Click: Next
- Check: I will create the page fields........Click: Next
- Select the data range....Click: Add....Click: Finish
- Remove the Column and Row fields
- Double-click the Value amount (to create a new sheet with the UN-pivoted data)
- Remove the first two columns
- Use Remove Duplicates (on the table ribbon tab)

Is that something you can work with?

 

[Africat]

Hi Ron,

Thanks for the response. I'm not sure if I have followed all the steps correctly.
I can get the unique list of colors but I am having difficulty creating a pivot table relating the two.

Ideally I am trying to make a pivot table below:
When Blue is expanded it shows only item 1 since it requires Blue
When Black is expanded it shows only item 2 since it requires Black
When Yellow is expanded it shows item 1 and item 2 since both require Yellow
I don't have trouble doing this if for example each item only has Colour1, but when trying to also relate all 8 colours i seem to be having difficulty.

UniqueColour Pivot
Blue
	Item 1
Black
	Item 2
Yellow
	Item 1
	Item 2
....

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
</tbody>

The only other way I can think of doing this is by rearranging the fields
Item Column
Item 1 - Blue
Item 1 - Yellow
Item

 

[Africat]

Continued...
The only other way I can think of doing this is by rearranging the fields
Item Column Colour
Item 1 Blue
Item 1 Yellow
Item 1 Purple
.....

But i would like to keep the item column as a key field with no duplicates

 

[Ron Coderre]

If you used Power Query...Getting the grouping is easy:

let
    Source = Excel.CurrentWorkbook(){[Name="rngColors"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ItemList", type text}, {"Colour1", type text}, {"Colour2", type text}, {"Colour3", type text}, {"Colour4", type text}, {"Colour5", type text}, {"Colour6", type text}, {"Colour7", type text}, {"Colour8", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ItemList"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Value"}, {{"Colors", each _, type table}}),
    #"Expanded Colors" = Table.ExpandTableColumn(#"Grouped Rows", "Colors", {"ItemList"}, {"ItemList"})
in
    #"Expanded Colors"

Then, if you want that data in a pivot table....
- Insert.Pivot_Table
- Use an External Data source
....Select the query you built
....Click: Open
....Continue building the pivot table as you normally would by putting the colors and items in ROWS

Does that help?