Transpose data ready for pivot table

[mattchewie]

Hi all,

I have data on 3 different tabs. 1 for stock on hand, 1 for sales, and i need to convert them to the the way i have it on the "Data for Pivot" tab. The data i have cannot be changed as this is the format i get it in. I basically want to combine SOH and sales by store, but then be able to have it in a line listing for a pivot table.
I have tried transposing it which works, but i need the line duplicated to show all models. The real data i have has around 200 stores, and 500 models, so i need a dump and process method... ideas?

an example below:

Stock on hand tab

	Store A	Store B	Store C	Store D
Model 1	1	2	3	4
Model 2	1	2	3	4
Model 3	1	2	3	4
Model 4	1	2	3	4

<tbody>
</tbody>

Sales Tab

	Store A	Store B	Store C	Store D
Model 1	1	2	1	2
Model 2	1	2	1	2
Model 3	1	2	1	2
Model 4	1	2	1	2

<tbody>
</tbody>

Store	Model	SOH	Sales
Store A	Model 1	1	1
Store A	Model 2	1	1
Store A	Model 3	1	1
Store A	Model 4	1	1
Store B	Model 1	2	2
Store B	Model 2	2	2
Store B	Model 3	2	2
Store B	Model 4	2	2
Store C	Model 1	3	1
Store C	Model 2	3	1
Store C	Model 3	3	1
Store C	Model 4	3	1
Store D	Model 1	4	2
Store D	Model 2	4	2
Store D	Model 3	4	2
Store D	Model 4	4	2

<tbody>
</tbody>

 

[sandy666]

try PowerQuery

Model

Store A

Store B

Store C

Store D

Model

Store A

Store B

Store C

Store D

store

Model

soh

sales

Model 1

1​

2​

3​

4​

Model 1

1​

2​

1​

2​

Store A

Model 2

1​

1​

Model 2

1​

2​

3​

4​

Model 2

1​

2​

1​

2​

Store A

Model 4

1​

1​

Model 3

1​

2​

3​

4​

Model 3

1​

2​

1​

2​

Store A

Model 3

1​

1​

Model 4

1​

2​

3​

4​

Model 4

1​

2​

1​

2​

Store A

Model 1

1​

1​

Store B

Model 3

2​

2​

Store B

Model 4

2​

2​

Store B

Model 1

2​

2​

Store B

Model 2

2​

2​

Store C

Model 3

3​

1​

Store C

Model 4

3​

1​

Store C

Model 1

3​

1​

Store C

Model 2

3​

1​

Store D

Model 4

4​

2​

Store D

Model 1

4​

2​

Store D

Model 2

4​

2​

Store D

Model 3

4​

2​

UnPivot each table then Merge them


Merge:

[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"Model", "Attribute"},Table2,{"Model", "Attribute"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Value"}, {"Table2.Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table2",{"Attribute", "Model", "Value", "Table2.Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "soh"}, {"Table2.Value", "sales"}, {"Attribute", "store"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"store", Order.Ascending}})
in
    #"Sorted Rows"[/SIZE]