Transpose with power query

[linkn00]

Hello,

Is there a way to do this transformation using power query? Help is extremely appreciated

order number	item
1​	a
1​	a
1​	a
2​	b
2​	b
2​	c
2​	d
result
Order number
1​	a	a	a
2​	b	b	c	d

 

[jdellasala]

Select the range and go to Data -> and click From Table/Range. It will default to changing the range to a table that looks like this:

Book1
	A	B	C	D	E
2	order number	item
3	1	a
4	1	a
5	1	a
6	2	b
7	2	b
8	2	c
9	2	d
10
11	result
12	Order number
13	1	a	a	a
14	2	b	b	c	d
Sheet1

Alternately, if you select the entire range and name it something like Data, the source will not be changed.
Either way, in Power Query go to Transform tab and click the Transpose button. The resulting table in both cases will look like this:

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13
2	order number	1	1	1	2	2	2	2		result	Order number	1	2
3	item	a	a	a	b	b	c	d				a	b
4												a	b
5												a	c
6													d
Sheet2

 

[GraH]

The sample suggests the order number is sorted.
If that is the case, one can use a group by, with the options groupkind.local. That's a fast grouping.
As aggregation using all rows, named Sub or something Aline. Add a custom column with the formula table.selectcolumns([ Sub ],"Item"). Wrap it in table.transpose. Expand the table.

 

[linkn00]

Maybe I didn't explain it correctly. I need to change A1:B8 to something that looks like A13:E14

 

[linkn00]

The sample suggests the order number is sorted.
If that is the case, one can use a group by, with the options groupkind.local. That's a fast grouping.
As aggregation using all rows, named Sub or something Aline. Add a custom column with the formula table.selectcolumns(_{,"Item"). Wrap it in table.transpose. Expand the table.}

could you please explain to me in detail? i am new to this power query

 

[linkn00]

could you please explain to me in detail? i am new to this power query

 

[jdellasala]

My bad. Sometimes I miss the details.
Going from this table:

Book1
	A	B
1	order number	item
2	1	a
3	1	a
4	1	a
5	2	b
6	2	b
7	2	c
8	2	d
Sheet1

Load it into Power Query, using this M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"order number"}, {{"All Rows", each _, type table [order number=text, item=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.RemoveColumns([All Rows],"order number")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose( [Custom] )),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"All Rows", "Custom"})
in
    #"Removed Columns"

Resulting in this table:

Book1
	D	E	F	G	H
1	order number	Column1	Column2	Column3	Column4
2	1	a	a	a
3	2	b	b	c	d
Sheet1

The two custom columns had to be hand coded, and no doubt there may be a better way to do it, but I think (HOPE!) this answers the question.

Depending on how large the actual data is, this MIGHT be as easy to do with 365 Array formulas, but if the data is large or reoccurring, Power Query is the way to go.

 

[linkn00]

Thank you so much this is amazing. One last question if you could please help me?
How can I compare the row to see if all of the items are the same?
in this case Order number 2, all items are not the same

Basically, I am trying to find the order number with different items.

 

[GraH]

If the goal is looking for different items, you do not need to transpose.
After the grouping, add a custom column with a formula like List.Distinct([All Rows][Item]).
Depending of your need, wrap this inside List.Count. you can even add a condition the count needs to be one to have true or false as the result.
Without excel for the moment, but maybe others can provide the code. Team work here...

 

[GraH]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"order number", Int64.Type}, {"item", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"order number"}, {{"Items", each List.Count(List.Distinct([item]))=1, type nullable logical}}, GroupKind.Local)
in
    #"Grouped Rows"