lunes, 4 de enero de 2016

Power Query: Agrupar por.

Días atrás un lector planteaba la siguiente cuestión:
...Necesito calcular el tiempo que una persona ha trabajado a lo largo de su vida en diferentes trabajos a los efectos de calcular si llega a la cantidad de años requeridos por la normativa uruguaya para jubilarse o retirarse de sus actividades laborales. Hasta ahí no habría problemas porque colocando en dos columnas la fecha de inicio y finalización de cada tarea y aplicando la función de sumar fechas, obtuve de forma individual cada periodo y la suma de todos al final. El problema es que hay personas que trabajaron simultáneamente en dos trabajos y por periodos diferentes de tiempo, y la normativa indica que los periodos superpuestos de tiempo no se suman, es decir, si una persona trabajo en un empleo del 1/1/14 al 31/12/14 y en el otro del 1/7/14 al 30/6/15, Excel me dice que trabajo en cada empleo por el periodo de un año, por lo tanto en la suma final me dice que trabajo 2 años, pero en realidad 6 meses se superponen (del 1/7/14 al 31/12/14) y a los efectos de contabilizar los años trabajados, ese periodo no se contabiliza, por lo que no debería ser sumado.


En el post de hoy daremos una solución empleando Power Query, y en particular una de sus funcionalidades más potentes: Agrupar por.

La idea, para dar respuesta al lector, consistirá en llegar para cada trabajador a la fecha de inicio más baja de entre todos sus trabajos, y de forma similar a la fecha más alta de entre todas las fechas de baja de entre todos sus trabajos...
De esta forma evitaremos duplicidades en el conteo de días, tal como exponía el lector.

Por ejemplo, partamos del siguiente listado de empleados:

Power Query: Agrupar por.



Si nos centramos en uno de los trabajadores (empleado1) vemos que ha desarrollado su trabajo en dos empresas distintas, y en tiempos diferentes... si bien, existe una superposición de periodos en los que ha trabajado al mismo tiempo en esas dos empresas:
empleado1 empresa1 01/01/2015 31/12/2015
empleado1 empresa2 01/07/2015 31/01/2016

en concreto desde el 01/07/2015 al 31/12/2015, i.e., seis meses que no se deben contabilizar de manera doble!!.


Una posible solución la encontramos con Power Query, para ello, seleccionamos nuestra tabla con el listado de trabajadores (llamada 'TblOrigen') y desde la ficha Power Query > grupo Datos de Excel > botón Desde Tabla, accederemos al editor de consultas de PQ.

Ya dentro del Editor de consultas, desde la Ficha Inicio > grupo Transformar > botón Agrupar por configuraremos, en la ventana diálogo correspondiente, los criterios de la agrupación:



Como se observa hemos indicado cuál es nuestro criterio de agrupación de registros: 'cuando el elemento del campo empleados coincida'; además hemos aplicado un cálculo sobre los campos tipo Fecha de Inicio y Fin (mínimo y máximo respectivamente).
Tras Aceptar este sería el resultado... un registro único para cada empleado, y la fecha mínima y máxima de entre todos sus trabajos.

Aunque ya podríamos trabajar con esa información, añadiremos una Nueva columna en nuestra tabla, empleando la herramienta 'Agregar columna personalizada' desde la ficha Agregar columna > grupo General:

Power Query: Agrupar por.



Notemos el sencillo cálculo para llegar al dato requerido... simplemente restaremos la Fecha más alta de las fechas de baja (llamada MaxFin) a la Fecha inferior de entre las fechas de alta (llamada MinInicio).

Al Aceptar, y Cerrar y Cargar el complemento, obtenemos la siguiente tabla en una de nuestras hojas de Excel:

Power Query: Agrupar por.



En esta tabla comprobamos cómo el cálculo ahora es el correcto para todos los casos, sin duplicidades...

OJO!!, esta forma de trabajar únicamente sería válida para casos de continuidad en los trabajos, esto es, que no haya intervalos de tiempo sin empleo.

1 comentario:

  1. Bueno! powerquery y powerpivot son un gran paso adelante.

    ResponderEliminar