Rows to Columns

[hasanlianar]

Hi,

Is it possible to convert rows to columns using Power Query, as in the example below:

FROM:

Code	Type
123	a
123	b
123	c
456	d
456	e
789	f
789	g

<tbody>
</tbody>

TO:

Code	Type1	Type2	Type3
123	a	b	c
456	d	e
789	f	g

<tbody>
</tbody>

Thanks

 

[RoryA]

There are probably lots of ways to do it, but I don't think any are that obvious. Here's one:

First make sure your table is sorted on the Code column. Add an index column to your table in PQ, starting at 0, then group that table by Code calculating the Min of the new index column. Save that as a connection only query.
Load your table into PQ again and merge it into the provious query using the Code field. Expand the table produced for each row, keeping just the Min value.
Now add a new index field, starting at 1 this time, and then create a calculated column subtracting the Min value from the index field. That should give you effectively a counter for each code, starting at 1.
You can then pivot the new counter field, using Type as the value field and specifying 'Don't aggregate' in the advanced options.

 

[hasanlianar]

Thank you for the solution. I thought conversion can be achieved with just a few clicks in PQ.
I followed your steps, though I could not end up with the desired result/

 

[RoryA]

I've uploaded a sample file using the data you posted here: https://1drv.ms/x/s!AsDYl-5bgVQWgYFWCYpZw0Xk-XT-mw
You should be able to view the steps used.

 

[hasanlianar]

I've uploaded a sample file using the data you posted here: https://1drv.ms/x/s!AsDYl-5bgVQWgYFWCYpZw0Xk-XT-mw
You should be able to view the steps used.

Thank you so much. It is ok now. The only missing step was that I did not delete additional columns before making pivot.