Help with pivot tables

[Stanley2020]

HI,

I am collecting data using Microsoft forms and the data is automatically dumped into an Excel spreadsheet as shown below in Table 1. I am wanting to pivot this table so that the data is presented as shown in Table 2, but I can not work out how to do it.

Can anyone help, please?

Spreadsheet Raw Data - Table 1

Timestamp	Pupil 1	Pupil 2	Pupil 3	Pupil 4	Service	Route Number​
01 October 2022	Yes	No	Yes	Yes	AM	3​
01 October 2022	Yes	Yes	Yes	No	PM	3​
02 October 2022	Yes	Yes	Yes	Yes	AM	3​
02 October 2022	Yes	Yes	Yes	Yes	PM	3​
03 October 2022	Yes	Yes	Yes	Yes	AM	3​
03 October 2022	Yes	No	No	Yes	PM	3​

Spreadsheet Pivot - Table 2

Route Number
3​
	Service
	AM
		Pupils name	01-Oct-22	02-Oct-22	03-Oct-22
		Pupil 1	Yes	Yes	Yes
		Pupil 2	No	Yes	Yes
		Pupil 3	Yes	Yes	Yes
		Pupil 4	Yes	Yes	Yes
	PM
		Pupils name	01-Oct-22	02-Oct-22	03-Oct-22
		Pupil 1	Yes	Yes	Yes
		Pupil 2	Yes	Yes	No
		Pupil 3	Yes	Yes	No
		Pupil 4	No	Yes	Yes

 

[arthurbr]

The easiest way is to "unpivot" your original table with Power Query then create the desired PT with the obtained lists