Transpose? Pivot? Unpivot?

[yetanotherusern]

With PowerQuery how can I get from this

Header	ID
Comment from X	1
Group 1	2
Posted Aug 8, 2022	3
ID12322	4
Comment from John	1
Group 2	2
Posted Sep 6, 2022	3
ID123	4
Comment from John	1
Group 2	2
Posted Sep 12, 2022	3
ID12323	4

 To

1	2	3	4
Comment from X	Group 1	Posted Aug 8, 2022	ID12322
Comment from John	Group 2	Posted Sep 6, 2022	ID123
Comment from John	Group 2	Posted Sep 12, 2022	ID12323

Thank you so much

[TomMartens · Accepted solution]

Hey @yetanotherusern 

you can use the Pivot Operation, but first you have to prepare the table:

1. remove the empty rows
2. add an index column
3. create a grouping column (a custom column), this one is necessary, otherwise Pivot will not work

This is how the table will look after these steps:

This is the formula for the custom column.

Number.RoundUp( [Index] / 4 )

Then i removed the Index column, marked the ID column and use the transform Pivot configured like this:

And here is the result:

Hopefully, this provides what you are looking for.

Regards,
Tom

[yetanotherusern]

Herzlichen Danke - wirklich toll! 

(Heartfelt thank you - really cool)