Compressing a week by week report into a month's/quarter/year report

[Shivex]

Hello!

In my work, we work with a check in system that is not changeable on the backend of it, but on many occasions we have people not checking in or out properly.
Ending up in not showing the correct number of hours worked, to make sure the employees get paid out properly. I export the check-in/report on a weekly basis, adjust the times people haven't checked in properly and send that off to administration.
In my line of work, Hospitality, we work with a lot of fluctuating ours but also times we travel for the company, which of course, are not registered on our system, which I also add manually.
I'm familiar with Powerquery, but not enough yet to figure this out on my own.

Anyone able to present and explain to me a solution?
(all names here are randomly generated for demonstration purposes)

ReporteControlDeAcceso20230319 - Copy.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Sucursal: COMPANY NAME
2		Reporte del: 2023-03-13 al 2023-03-20
3		Reporte generado: 2023-03-25 10:45:35
4
5		Reporte Control De Acceso
6
7					Lun.	Mar.	Mié.	Jue.	Vie.	Sáb.	Dom.
8	No. Empleado	Nombre	Usuario	Perfil	13-Mar.	14-Mar.	15-Mar.	16-Mar.	17-Mar.	18-Mar.	19-Mar.	Total	Días laborados
9	001	illie Sanders	Illie	HEADBARTENDER	8.90	10.53	0.00	9.95	9.89	10.13	0.00	49.40	5.15
10	002	Ella-Louise Wyatt	Ella-Louise	BARTENDER	18.31	9.45	9.28	7.05	10.81	0.00	0.00	54.90	5.72
11	003	Neave Thomson	Neave	BARTENDER	8.91	10.53	0.00	10.09	9.73	9.64	0.00	48.90	5.09
12	004	Alessandro Bradshaw	Alessandro	BARTENDER	8.87	0.00	8.94	9.03	11.46	9.02	8.95	56.27	5.86
13	005	Lila Stone	Lila	BARTENDER	8.00	8.63	0.00	9.51	9.16	9.91	4.16	49.37	5.14
14	006	Ilyas Jenkins	Ilyas	BARTENDER	8.61	10.74	0.00	10.02	9.83	9.91	10.21	59.32	6.18
15	007	Herbie Nelson	Herbie	BARTENDER	8.76	0.00	0.00	9.99	10.16	0.00	0.00	28.91	3.01
16	008	India Peck	India	BARTENDER	8.88	0.00	9.26	0.00	0.00	8.76	8.76	35.66	3.71
17	009	Adele Davila	Adele	MESERO	6.31	0.00	5.73	7.28	6.14	6.56	6.77	38.79	4.04
18	010	Brooke Luna	Brooke	HEADBARTENDER	0.00	12.20	8.56	7.72	13.05	10.57	13.99	66.10	6.89
Control de acceso

Cell Formulas
Range	Formula
L9:L18	L9	=SUM(E9:K9)
M9:M18	M9	=L9/9.6

 

[James006]

Hi,
Since you are using Power Query, you can use Group By

How to GROUP BY or summarize rows - Power Query

In Power Query, you can group or summarize the values in various rows into a single value by grouping the rows according to the values in one or more columns. Power Query has two types of Group By operations: aggregate a column with an aggregate function, or perform an operation.

learn.microsoft.com

 

[Shivex]

Hi,
Since you are using Power Query, you can use Group By

How to GROUP BY or summarize rows - Power Query

In Power Query, you can group or summarize the values in various rows into a single value by grouping the rows according to the values in one or more columns. Power Query has two types of Group By operations: aggregate a column with an aggregate function, or perform an operation.

learn.microsoft.com

Great!
I'll give that a try, thank you!

 

[Shivex]

Hi,
Since you are using Power Query, you can use Group By

How to GROUP BY or summarize rows - Power Query

In Power Query, you can group or summarize the values in various rows into a single value by grouping the rows according to the values in one or more columns. Power Query has two types of Group By operations: aggregate a column with an aggregate function, or perform an operation.

learn.microsoft.com

I've been playing around a bit with the group function, I am able to get the total number of worked days; however, I can't seem to figure out how to get the option to chose dates or a range of dates.
In the pictures below, you see how the data from multiple weekly exports gets loaded into power query.
These are all stacked neatly under each other as it loads the data, how am I able to have Power Query differentiate between those dates?

 

[James006]

Hi,
For a smart solution to list dates between dates in Power Query :

M / Power Query help for table merge (Conditional Cross Join)

Hi M experts, I have a Tasks table with following data: Task Name Start End A 01/08/2018 03/08/2018 B 07/08/2018 17/08/2018 C 21/08/2018 31/08/2018 I have a Calendar table that spans 01/01/2018 to 31/12/2018. I wanted to get one line per day for a given task between Start and End Date...

community.powerbi.com