Combine data in power bi from excel

[HSK25]

Team,

Ihave a excel file containing the data of attendence of workforce. each file has sheets named as months and each sheet contains the date wise head count. but the problem is the date is spread across the row instead of columns. moreover after each date there is a column named remarks. How to combine the date for last six years in power bi.

[HSK25]

Sl No	Location	Project Code	PO No	Activity	01.10.24	Remarks	02.10.24	Remarks	03.10.24	Remarks	04.10.24	Remarks	05.10.24	Remarks	06.10.24	Remarks	07.10.24	Remarks	08.10.24	Remarks	09.10.24	Remarks	10.10.24	Remarks	11.10.24	Remarks
1	FMM	IN0024	4700087289/102	Scaffolding(OT)	463	scaff-335, sup-70, safety-14, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30	378	scaff-265, sup-60, safety-14, Mgr-1, safety mgr-0, Hr-1, Arear Mgr-5, admin-2, driver-30	490	scaff-360, sup-70, safety-16, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30	493	scaff-365, sup-70, safety-14, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30	497	scaff-365, sup-71, safety-17, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30	385	scaff-280, sup-56, safety-12, Mgr-1, safety mgr-0, Hr-0, Arear Mgr-6, admin-0, driver-30	492	scaff-360, sup-71, safety-17, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30	486	scaff-355, sup-71, safety-17, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-4, driver-30	427	scaff-305, sup-61, safety-17, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30	368	scaff-255, sup-58, safety-13, Mgr-1, safety mgr-1, Hr-0, Arear Mgr-6, admin-4, driver-30	299	scaff-210, sup-47, safety-6, Mgr-1, safety mgr-1, Hr-0, Arear Mgr-1, admin-3, driver-30
		B'Shift			0		0		0		0		0		0		0		0		0		0		0
		Night			101	scaff-71, sup-15, safety -3, Arear Mgr-1,  driver-11	98	scaff-73, sup-11, safety -4, Arear Mgr-0,  driver-10	142	scaff-102, sup-22, safety -3, Arear Mgr-1,  driver-14	180	scaff-140, sup-22, safety -3, Arear Mgr-1,  driver-14	206	scaff-161, sup-25, safety -3, Arear Mgr-1,  driver-16	204	scaff-166, sup-25, safety -2, Arear Mgr-0,  driver-11	190	scaff-149, sup-23, safety -2, Arear Mgr-1,  driver-15	176	scaff-134, sup-23, safety -2, Arear Mgr-1,  driver-16	105	scaff-79, sup-10, safety -2, Arear Mgr-1,  driver-13	73	scaff-46, sup-10, safety -2, Arear Mgr-1,  driver-14	35	scaff-14, sup-5, safety -2, Arear Mgr-1,  driver-13
2	CRM BARA	IN0017	4700087296/102	Scaffolding	9	scaff-7, sup-1, safety-1	0		9	scaff-7, sup-1, safety-1	9	scaff-7, sup-1, safety-1	9	scaff-7, sup-1, safety-1	0		9	scaff-7, sup-1, safety-1	8	scaff-6, sup-1, safety-1	9	scaff-7, sup-1, safety-1	14	scaff-12, sup-1, safety-1	13	scaff-11, sup-1, safety-1
		B'Shift			0		0		0		0		0		0		0		0		0		0		0
3	PELLET PLANT	IN0135	3000147182	Scaffolding	11	scaff-9, sup-2, safety-0, admin-0	10	scaff-8, sup-2, safety-0, admin-0	11	scaff-9, sup-2, safety-0, admin-0	12	scaff-10, sup-2, safety-0, admin-0	13	scaff-10, sup-2, safety-0, admin-1	0		5	scaff-4, sup-1, safety-0, admin-0	10	scaff-8, sup-1, safety-1, admin-0	9	scaff-7, sup-1, safety-1, admin-0	8	scaff-6, sup-1, safety-0, admin-1	8	scaff-6, sup-1, safety-0, admin-1
		B+Night			0		0		0		0		0		0		0		0		0		0		0
4	FMM (Aluminium Scaffolding)	IN0418	4700114412	Scaffolding	10	scaff-4, sup-2, Safety-1, driver-2, Admin-1	10	scaff-4, sup-2, Safety-1, driver-2, Admin-1	11	scaff-5, sup-2, Safety-1, driver-2, Admin-1	11	scaff-5, sup-2, Safety-1, driver-2, Admin-1	11	scaff-5, sup-2, Safety-1, driver-2, Admin-1	0		10	scaff-4, sup-2, Safety-1, driver-2, Admin-1	11	scaff-5, sup-2, Safety-1, driver-2, Admin-1	11	scaff-5, sup-2, Safety-1, driver-2, Admin-1	11	scaff-5, sup-2, Safety-1, driver-2, Admin-1	11	scaff-5, sup-2, Safety-1, driver-2, Admin-1
		B Shift			0		0		0		0		0		0		0		0		0		0		0
		Night			0		0		0		0		0		0		0		0		0		0		0
					594		496		663		705		736		589		706		691		561		474		366

[Omid_Motamedise]

what is your desired result over this table"?

[HSK25]

Thanks for asking.

I want a consolidated table containing Days, Designation and the headcount.

[shafiz_p]

Hi @HSK25  Kindly share representative data using cloud if possible.