Power Query? Changing Format of Table

[Gwill1983]

Hi all,

I am trying to find a way to change my table from this:

Employee Name	01 Jan (Mon)	02 Jan (Tue)	03 Jan (Wed)	04 Jan (Thu)	05 Jan (Fri)	06 Jan (Sat)	07 Jan (Sun)
Joe Bloggs	Statutory Holidays - New Years Day					Weekend	Weekend
Dave Smith	Statutory Holidays - New Years Day			Weekend		Weekend	Weekend
Kelly Jones	Statutory Holidays - New Years Day					Weekend	Weekend

To this:

Date	Joe Bloggs	Dave Smith	Kelly Jones
01/01/2024​	Statutory Holidays - New Years Day	Statutory Holidays - New Years Day	Statutory Holidays - New Years Day
02/01/2024​
03/01/2024​
04/01/2024​		Weekend
05/01/2024​
06/01/2024​	Weekend	Weekend	Weekend
07/01/2024​	Weekend	Weekend	Weekend

I have tried to use the transpose option within power query, but this does not pull through the heading (date) into the first column so I end up with something like this:

Joe Bloggs	Dave Smith	Kelly Jones
Statutory Holidays - New Years Day	Statutory Holidays - New Years Day	Statutory Holidays - New Years Day
	Weekend
Weekend	Weekend	Weekend
Weekend	Weekend	Weekend

Its beginning to drive me crazy, so any help to solve this would be amazing!

 

[RoryA]

Choose to 'use headers as first row' first, then transpose, then put the headers back. You'll probably want to rename the first column.

 

[Gwill1983]

Brilliant! This worked perfectly. Thank you

 

[RoryA]

Glad we could help.