Power Query help - transpose

[ashley1984]

Hi,

I'm trying to transpose a column with several data points (Sales Value, Sales Volume, Unit Price etc) into separate columns, so a column for each data point.

I also have weekly dates (data is pulled off weekly and currently runs for 104 weeks) that the data needs to then correspond back with. I have uploaded some images of the data, the first image should be what the data currently looks like and the second how I want it be. I've changed the data into a table and opened Power Query and tried the Unpivot option but not had much luck.

Any help would be appreciated.

 

[sandy666]

post data in copyable form, eg. via XL2BB

 

[ashley1984]

post data in copyable form, eg. via XL2BB

Is this better? The data how it currently is:

PowerQuery Help.xlsx
	B	C	D	E	F	G	H
3	Product code	Item Name	Measure	03/06/2019	10/06/2019	17/06/2019	17/06/20192
4	6420	Product A	Sales Units	125,955	129,466	59,905	48,879
5	1188	Product A	Sales Value	£249,361	£254,021	£135,676	£120,803
6	9070	Product A	Product Price	£ 1.98	£ 1.96	£ 2.26	£ 2.47
7	8552	Product B	Sales Units	24,607	26,978	68,211	95,584
8	3126	Product B	Sales Value	£60,819	£66,077	£131,300	£177,797
9	7476	Product B	Product Price	£ 2.47	£ 2.45	£ 1.92	£ 1.86
10	7330	Product C	Sales Units	20,380	19,813	16,314	16,757
11	4470	Product C	Sales Value	£50,445	£49,134	£40,505	£41,383
12	6467	Product C	Product Price	£ 2.48	£ 2.48	£ 2.48	£ 2.47
13	4131	Product D	Sales Units	19,610	18,026	6,956	4,659
14	1447	Product D	Sales Value	£37,353	£35,334	£15,046	£11,083
15	3086	Product D	Product Price	£ 1.90	£ 1.96	£ 2.16	£ 2.38
Current Data

Cell Formulas
Range	Formula
E6:H6,E15:H15,E12:H12,E9:H9	E6	=E5/E4

and the data how I want it post Power Query:

PowerQuery Help.xlsx
	B	C	D	E	F	G
3	Product code	Item Name	Date	Sales Value	Sales Units	Product Price
4	9070	Product A	03/06/2019	£ 249,361.11	125,955	£ 1.98
5	7476	Product B	03/06/2019	£ 60,819.45	24,607	£ 2.47
6	6467	Product C	03/06/2019	£ 50,445.35	20,380	£ 2.48
7	3086	Product D	03/06/2019	£ 37,352.72	19,610	£ 1.90
8	9070	Product A	10/06/2019	£ 254,020.73	129,466	£ 1.96
9	7476	Product B	10/06/2019	£ 66,076.67	26,978	£ 2.45
10	6467	Product C	10/06/2019	£ 49,133.81	19,813	£ 2.48
11	3086	Product D	10/06/2019	£ 35,334.04	18,026	£ 1.96
12	9070	Product A	17/06/2019	£ 135,676.01	59,905	£ 2.26
13	7476	Product B	17/06/2019	£ 131,299.93	68,211	£ 1.92
14	6467	Product C	17/06/2019	£ 40,504.66	16,314	£ 2.48
15	3086	Product D	17/06/2019	£ 15,045.71	6,956	£ 2.16
16	9070	Product A	17/06/2019	£ 120,802.65	48,879	£ 2.47
17	7476	Product B	17/06/2019	£ 177,797.00	95,584	£ 1.86
18	6467	Product C	17/06/2019	£ 41,382.64	16,757	£ 2.47
19	3086	Product D	17/06/2019	£ 11,083.41	4,659	£ 2.38
To Be Data

 

[sandy666]

Unpivot columns with Dates via PQ then from the ribbon: Insert - Pivot Table

 

[sandy666]

or

Item Name	Attribute	Sales U	Sales V	Prod Price
Product A	03/06/2019	125955	249361.11	1.979763487
Product A	10/06/2019	129466	254020.73	1.962065175
Product A	17/06/2019	108784	256478.66	4.736316055
Product B	03/06/2019	24607	60819.45	2.471632056
Product B	10/06/2019	26978	66076.67	2.449279784
Product B	17/06/2019	163795	309096.93	3.785021017
Product C	03/06/2019	20380	50445.35	2.475237978
Product C	10/06/2019	19813	49133.81	2.479877353
Product C	17/06/2019	33071	81887.3	4.952389299
Product D	03/06/2019	19610	37352.72	1.904779194
Product D	10/06/2019	18026	35334.04	1.960170864
Product D	17/06/2019	11615	26129.12	4.541907698

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.Unpivot(Source, {"03/06/2019", "10/06/2019", "17/06/2019", " 17/06/2019"}, "Attribute", "Value"),
    Type = Table.TransformColumnTypes(Unpivot,{{"Attribute", type date}}),
    Pivot = Table.Pivot(Type, List.Distinct(Type[Measure]), "Measure", "Value", List.Sum),
    Group = Table.Group(Pivot, {"Item Name", "Attribute"}, {{"Sales U", each List.Sum([Sales Units]), type number}, {"Sales V", each List.Sum([Sales Value]), type number}, {"Prod Price", each List.Sum([Product Price]), type number}}),
    Sort = Table.Sort(Group,{{"Item Name", Order.Ascending}, {"Attribute", Order.Ascending}})
in
    Sort

or Pivot Attribute by Value

 

[sandy666]

with rounded values and proper sorting

Item Name	Attribute	Sales U	Sales V	Prod Price
Product A	03/06/2019	125955	249361.11	1.98
Product B	03/06/2019	24607	60819.45	2.47
Product C	03/06/2019	20380	50445.35	2.48
Product D	03/06/2019	19610	37352.72	1.9
Product A	10/06/2019	129466	254020.73	1.96
Product B	10/06/2019	26978	66076.67	2.45
Product C	10/06/2019	19813	49133.81	2.48
Product D	10/06/2019	18026	35334.04	1.96
Product A	17/06/2019	108784	256478.66	4.74
Product B	17/06/2019	163795	309096.93	3.79
Product C	17/06/2019	33071	81887.3	4.95
Product D	17/06/2019	11615	26129.12	4.54

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.Unpivot(Source, {"03/06/2019", "10/06/2019", "17/06/2019", " 17/06/2019"}, "Attribute", "Value"),
    Type = Table.TransformColumnTypes(Unpivot,{{"Attribute", type date}}),
    Pivot = Table.Pivot(Type, List.Distinct(Type[Measure]), "Measure", "Value", List.Sum),
    Group = Table.Group(Pivot, {"Item Name", "Attribute"}, {{"Sales U", each List.Sum([Sales Units]), type number}, {"Sales V", each List.Sum([Sales Value]), type number}, {"Prod Price", each List.Sum([Product Price]), type number}}),
    Round = Table.TransformColumns(Group,{{"Sales U", each Number.Round(_, 2), type number}, {"Sales V", each Number.Round(_, 2), type number}, {"Prod Price", each Number.Round(_, 2), type number}}),
    Sort = Table.Sort(Round,{{"Attribute", Order.Ascending}, {"Item Name", Order.Ascending}})
in
    Sort