Dynamic Column Headers from multiple rows

[Elscc]

Hi,

Thanks in advance for any help.

I am bringing data into power bi from excel. This is an export from a third party so i dont have any control over the source format. 

The data has some person information, then activitiy codes for AM and PM each day, for the last 2 working weeks. Example data below:

			08-03-2023		09-03-2023		10-03-2023
ID	Forename	Surname	AM	PM	AM	PM	AM	PM
1234	Joe	Bloggs	.	.	/	\	O	O

Within power bi power query I need to format the columns so that it ends up like the following:

ID	Forename	Surname	08-03-2023 AM	08-03-2023 PM	09-03-2023 AM	09-03-2023 PM	10-03-2023 AM	10-03-2023 PM
1234	Joe	Bloggs	.	.	/	\	O	O

Alternatively, the following format would also work, and mayybe even better:

ID	Forename	Surname	Date	AM PM	Code
1234	Joe	Bloggs	10/03/2023	AM	O
1234	Joe	Bloggs	10/03/2023	PM	O
1234	Joe	Bloggs	09/03/2023	AM	/
1234	Joe	Bloggs	09/03/2023	PM	\
1234	Joe	Bloggs	08/03/2023	AM	.
1234	Joe	Bloggs	08/03/2023	PM	.

I need to take into account that this export is taken weekly, and each time the dates are different, so I need to add AM and PM as suffixes to column headers which change. 

Its also complicated by the fact the person information are on the same row as the AM and PM, not the dates. 

I am happy to include the example source spreadsheet and desired outcome, and also a .pbix with the source already loaded, but it looks like uploading those files is not supported here. Please let me know how I should do this. 

Any help very much appreciated. 

[KNP · Accepted solution]

Have a look at the attached PBIX as a starting point.

It uses a function from Bill Szysz found on this blog post (https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-power... ) by @ImkeF.

Let me know if you need further help/explanation.

[Elscc]

This is great. Much appreciated!