Collapse Multiple Rows to Single Row

[GreenCat223]

Hi there!

I was wondering if someone could help explain why my data doesn't pivot in Power Query?

This is how it comes out of the system (Table 1):

EmpNum	ProductName	InputName	EntryValue	Element Entry ID	Element Entry ID Value
Emp1	Apples	Type	GrannySmith	123​	345​
Emp1	Apples	Units	5​	123​	347​
Emp1	Apples	Type	Gala	456​	349​
Emp1	Apples	Units	3​	456​	351​
Emp1	Apples	Type	Braeburn	789​	353​
Emp1	Apples	Units	1​	789​	355​
Emp1	Apples	Type	PinkLady	321​	357​
Emp1	Apples	Units	7​	321​	359​
Emp2	Oranges	Type	Satsuma	566​	756​
Emp2	Oranges	Units	6​	566​	758​
Emp3	Oranges	Type	Satsuma	788​	555​
Emp3	Oranges	Units	6​	788​	557​
Emp4	Pears	Units	5​	458​	997​
Emp5	Mango	Type	Chaunsa	432​	160​
Emp5	Mango	Units	7​	432​	162​
Emp5	Mango	Season	Summer	432​	164​
Emp6	Plum	Type	Plum	571​	166​

This is how I want the data to look like (Table 2):

EmpNum	ProductName	Type	Units	Season	Element Entry ID
Emp1	Apples	GrannySmith	5​		123​
Emp1	Apples	Gala	3​		456​
Emp1	Apples	Braeburn	1​		789​
Emp1	Apples	PinkLady	7​		321​
Emp5	Mango	Chaunsa	7​	Summer	432​
Emp2	Orange	Satsuma	6​		566​
Emp3	Orange	Satsuma	6​		788​
Emp4	Pears		5​		789​

What I don't understand is why this data (Table 3) is still expanded as Table 1 when I use the pivot feature in Power Query.

EmpNum	ProductName	Type	Element Entry ID Value	Units	Season	Element Entry ID
Emp1	Apples		351​	3​		456​
Emp1	Apples	Gala	349​			456​
Emp1	Apples		355​	1​		789​
Emp1	Apples	Braeburn	353​			789​
Emp1	Apples		347​	5​		123​
Emp1	Apples	GrannySmith	345​			123​
Emp1	Apples		359​	7​		321​
Emp1	Apples	PinkLady	357​			321​
Emp5	Mango	Chaunsa	160​			432​
Emp5	Mango		162​	7​		432​
Emp5	Mango		164​		Summer	432​
Emp3	Oranges		557​	6​		788​
Emp2	Oranges	Satsuma	756​			566​
Emp3	Oranges	Satsuma	555​			788​
Emp2	Oranges		758​	6​		566​
Emp4	Pears		997​	5​		458​
Emp6	Plum	Plum	166​			571​

I'm aware that if I remove the "Element Entry ID Value" column, prior to pivoting I will get the result I want in Table 2.

An explanation in laymen's terms would be really helpful so I can understand it better!

Thank you very much for your time.

 

[alansidman]

Share your Mcode for the example you have provided so it can be analyzed.

 

[GreenCat223]

This is the M-Code for my solution:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"EmpNum", type text}, {"ProductName", type text}, {"InputName", type text}, {"EntryValue", type any}, {"Element Entry ID", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[InputName]), "InputName", "EntryValue"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"EmpNum", "ProductName", "Type", "Units", "Season", "Element Entry ID"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"ProductName", Order.Ascending}})
in
#"Sorted Rows"

 

[RoryA]

Remove the Element Entry ID Value column before you unpivot. It is unique for every row, which stops the transformation from doing what you want.

 

[GreenCat223]

Thanks Rory, makes sense now...