Combine data in power bi from excel

Question

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.

shafiz_p · Answer

Hi&nbsp;HSK25&nbsp; Kindly share representative data using cloud if possible.&nbsp;

hsk25 · Answer

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sl NoLocationProject CodePO NoActivity01.10.24Remarks02.10.24Remarks03.10.24Remarks04.10.24Remarks05.10.24Remarks06.10.24Remarks07.10.24Remarks08.10.24Remarks09.10.24Remarks10.10.24Remarks11.10.24Remarks1FMMIN00244700087289/102Scaffolding(OT)463scaff-335, sup-70, safety-14, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30378scaff-265, sup-60, safety-14, Mgr-1, safety mgr-0, Hr-1, Arear Mgr-5, admin-2, driver-30490scaff-360, sup-70, safety-16, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30493scaff-365, sup-70, safety-14, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30497scaff-365, sup-71, safety-17, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30385scaff-280, sup-56, safety-12, Mgr-1, safety mgr-0, Hr-0, Arear Mgr-6, admin-0, driver-30492scaff-360, sup-71, safety-17, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30486scaff-355, sup-71, safety-17, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-4, driver-30427scaff-305, sup-61, safety-17, Mgr-1, safety mgr-1, Hr-1, Arear Mgr-6, admin-5, driver-30368scaff-255, sup-58, safety-13, Mgr-1, safety mgr-1, Hr-0, Arear Mgr-6, admin-4, driver-30299scaff-210, sup-47, safety-6, Mgr-1, safety mgr-1, Hr-0, Arear Mgr-1, admin-3, driver-30&nbsp;&nbsp;B'Shift&nbsp;&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;&nbsp;&nbsp;Night&nbsp;&nbsp;101scaff-71, sup-15, safety -3, Arear Mgr-1,&nbsp; driver-1198scaff-73, sup-11, safety -4, Arear Mgr-0,&nbsp; driver-10142scaff-102, sup-22, safety -3, Arear Mgr-1,&nbsp; driver-14180scaff-140, sup-22, safety -3, Arear Mgr-1,&nbsp; driver-14206scaff-161, sup-25, safety -3, Arear Mgr-1,&nbsp; driver-16204scaff-166, sup-25, safety -2, Arear Mgr-0,&nbsp; driver-11190scaff-149, sup-23, safety -2, Arear Mgr-1,&nbsp; driver-15176scaff-134, sup-23, safety -2, Arear Mgr-1,&nbsp; driver-16105scaff-79, sup-10, safety -2, Arear Mgr-1,&nbsp; driver-1373scaff-46, sup-10, safety -2, Arear Mgr-1,&nbsp; driver-1435scaff-14, sup-5, safety -2, Arear Mgr-1,&nbsp; driver-132CRM BARAIN0017&nbsp;4700087296/102Scaffolding9scaff-7, sup-1, safety-10&nbsp;9scaff-7, sup-1, safety-19scaff-7, sup-1, safety-19scaff-7, sup-1, safety-10&nbsp;9scaff-7, sup-1, safety-18scaff-6, sup-1, safety-19scaff-7, sup-1, safety-114scaff-12, sup-1, safety-113scaff-11, sup-1, safety-1&nbsp;&nbsp;B'Shift&nbsp;&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;3PELLET PLANTIN01353000147182Scaffolding11scaff-9, sup-2, safety-0, admin-010scaff-8, sup-2, safety-0, admin-011scaff-9, sup-2, safety-0, admin-012scaff-10, sup-2, safety-0, admin-013scaff-10, sup-2, safety-0, admin-10&nbsp;5scaff-4, sup-1, safety-0, admin-010scaff-8, sup-1, safety-1, admin-09scaff-7, sup-1, safety-1, admin-08scaff-6, sup-1, safety-0, admin-18scaff-6, sup-1, safety-0, admin-1&nbsp;&nbsp;B+Night&nbsp;&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;4FMM (Aluminium Scaffolding)IN04184700114412Scaffolding10scaff-4, sup-2, Safety-1, driver-2, Admin-110scaff-4, sup-2, Safety-1, driver-2, Admin-111scaff-5, sup-2, Safety-1, driver-2, Admin-111scaff-5, sup-2, Safety-1, driver-2, Admin-111scaff-5, sup-2, Safety-1, driver-2, Admin-10&nbsp;10scaff-4, sup-2, Safety-1, driver-2, Admin-111scaff-5, sup-2, Safety-1, driver-2, Admin-111scaff-5, sup-2, Safety-1, driver-2, Admin-111scaff-5, sup-2, Safety-1, driver-2, Admin-111scaff-5, sup-2, Safety-1, driver-2, Admin-1&nbsp;&nbsp;B Shift&nbsp;&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;&nbsp;&nbsp;Night&nbsp;&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;594&nbsp;496&nbsp;663&nbsp;705&nbsp;736&nbsp;589&nbsp;706&nbsp;691&nbsp;561&nbsp;474&nbsp;366&nbsp;

omid_motamedise · Answer

what is your desired result over this table"?

hsk25 · Answer

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

Forum Discussion

Combine data in power bi from excel

4 Replies

Related content

Tables combine

Develop Power Query outside Power BI or Excel

Power Query Combine Multiple Files From Folder

combine power bi reports

combining excel and power bi dataset in report builder


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