Weird formatting to make a report

[carlosflores]

Hello Community. Is there a way I can make a report using this kind of format?

This information is send by one client so he doesn't want to change the way he sends this reports, but I'd like to present them using Power BI. Is there a way I can format this into a Power BI friendly format?

Thanks in advance

[Ashish_Mathur]

Hi,

Yes.  It should be possible to arrange this data in a PowerBI data ready format.  Share the link from where i can download your MS Excel file.  Explain the Data, the headings and in which specific column should DNI go?

[carlosflores]

@Ashish_Mathur Thanks fr your response.

This is the link to download the file:

https://files.fm/u/s6pt9yku

I wish to make it look something like this

TRABAJADOR	DNI	Día	Fecha	Horario ingreso	Horario salida	Ingreso	salida
ACOSTA NEIRA, ANGELA KARINA	40689539	MAR	11/09/18	06:00	14:00	05:50:30	14:55:12
ACOSTA NEIRA, ANGELA KARINA	40689539	MIE	12/09/18	06:00	14:00	05:49:23	14:24:31
ACOSTA NEIRA, ANGELA KARINA	40689539	JUE	13/09/18	06:00	14:00	05:51:12	14:23:35
ACOSTA NEIRA, ANGELA KARINA	40689539	VIE	14/09/18	06:00	14:00	05:50:49	14:37:16
ACOSTA NEIRA, ANGELA KARINA	40689539	SAB	15/09/18	06:00	14:00	05:50:20	14:23:56
AGRAMONTE TEJEDA, EDUARDO MIGUEL	29631644	MAR	11/09/18	14:00	22:00	05:51:59	14:28:27
AGRAMONTE TEJEDA, EDUARDO MIGUEL	29631644	MIE	12/09/18	14:00	22:00	05:49:44	14:31:09
AGRAMONTE TEJEDA, EDUARDO MIGUEL	29631644	JUE	13/09/18	14:00	22:00	05:50:30	14:18:11
AGRAMONTE TEJEDA, EDUARDO MIGUEL	29631644	VIE	14/09/18	14:00	22:00	05:52:25	14:29:26
AGRAMONTE TEJEDA, EDUARDO MIGUEL	29631644	SAB	15/09/18	14:00	22:00	05:50:43	14:39:10
ALANOCA MENDOZA, LOURDES MARIBEL	46088109	MAR	11/09/18	22:00	06:00	06:18:17	21:53:36
ALANOCA MENDOZA, LOURDES MARIBEL	46088109	MIE	12/09/18	22:00	06:00	06:14:51	21:52:33
ALANOCA MENDOZA, LOURDES MARIBEL	46088109	JUE	13/09/18	22:00	06:00	06:15:47	21:57:21
ALANOCA MENDOZA, LOURDES MARIBEL	46088109	VIE	14/09/18	22:00	06:00	06:14:58	21:56:14

It seems to me it's more Power BI friendly this way. the DNI is like the worker's ID. "Horario ingreso" is when they're suposed to get in, "horario salida" is when they're suposed to leave work. "Ingreso" is when they actually get into work and "Salida" is when they actually get out of work.

Thanks for all your help!

[Ashish_Mathur]

Hi,

To clarify further, share the exact output that you are expecting if the source data range was from A16:K48.

[carlosflores]

@Ashish_Mathur thanks for your response,

If it were from A16:K48 it would be like this:

Name	Date	Start	End	Real start	Real end	DNI
VALVERDE SANTA MARIA, DAVID ARISTIDES	11/09/18	Sin Horario	Sin Horario	07:31:20		09831449
VALVERDE SANTA MARIA, DAVID ARISTIDES	12/09/18	Sin Horario	Sin Horario	07:44:23	18:59:00	09831449
VALVERDE SANTA MARIA, DAVID ARISTIDES	13/09/18	Sin Horario	Sin Horario	07:45:53		09831449
VALVERDE SANTA MARIA, DAVID ARISTIDES	14/09/18	Sin Horario	Sin Horario	07:31:55	18:06:06	09831449
AGUI MALAGA, MAX GUILLERMO	11/09/18	07:30	17:50	07:43:28	12:11:08	41629553
ALVAREZ CORNEJO, ADRIANA EUGENIA	11/09/18	07:30	17:50		18:21:51	41402624
ALVAREZ CORNEJO, ADRIANA EUGENIA	12/09/18	07:30	17:50		18:39:25	41402624
ALVAREZ CORNEJO, ADRIANA EUGENIA	13/09/18	07:30	17:50		18:59:36	41402624
ALVAREZ CORNEJO, ADRIANA EUGENIA	14/09/18	07:30	17:50		19:24:56	41402624
APAZA ALEJO, DIEGO EDSON	11/09/18	Sin Horario	Sin Horario	05:58:20	14:18:02	46478441
APAZA ALEJO, DIEGO EDSON	12/09/18	Sin Horario	Sin Horario	12:49:19	20:50:16	46478441
APAZA ALEJO, DIEGO EDSON	13/09/18	Sin Horario	Sin Horario	05:51:32	14:12:31	46478441
APAZA ALEJO, DIEGO EDSON	14/09/18	Sin Horario	Sin Horario	05:58:16	14:46:29	46478441
APAZA ALEJO, DIEGO EDSON	15/09/18	Sin Horario	Sin Horario	05:59:05	14:53:15	40299508
APAZA CAPA, YULIANA ANA	11/09/18	Sin Horario	Sin Horario	05:52:48	14:19:10	40299508
APAZA CAPA, YULIANA ANA	12/09/18	Sin Horario	Sin Horario	05:55:42	14:12:17	40299508
APAZA CAPA, YULIANA ANA	13/09/18	Sin Horario	Sin Horario	05:55:42	14:12:53	40299508
APAZA CAPA, YULIANA ANA	14/09/18	Sin Horario	Sin Horario	05:53:33	14:13:59	40299508
APAZA CAPA, YULIANA ANA	15/09/18	Sin Horario	Sin Horario	05:43:44	14:08:47	40299508

But the real source data range is everything, from A16:K.2669

Thanks again!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.