Power Query. Diferencia de horas

10. Diferencia de horas.rar

Descargar RAR • 34KB

En el siguiente ejemplo vamos a ver cómo podemos calcular la diferencia entre horas, es decir, tenemos tres personas con sus horas trabajadas desde una fecha de inicio y su fecha de fin.

Debemos de tener en cuenta cuando las horas pasan después de la medianoche.

En un archivo tenemos tres hojas, que corresponden a tres personas, donde cada uno tiene una hora de inicio y de fin.

Antes de pasar a power Query, vamos a hacerlo en Excel.

El primer trabajador es Claudia, y, estas son las horas de inicio y fin.

Para la primera jornada, voy a restar la de fin menos la hora inicio.

=C7-B7

Vemos que nos devuelve 0,5.

Pues vamos a encerrar esta operación entre paréntesis, y, multiplicamos por 24, para pasarlo a horas.

=(C7-B7)*24

Como resultado tenemos 12 horas, que es correcto, pues empezó a las ocho de la mañana y termino a las ocho de la tarde.

Arrastremos la formula.

Vemos que las horas son correctas.

Pero como tenemos que unir las tres hojas para crear una única tabla, lo que va a ser más útil usar power Query.

Pues vamos a ello.

Cierro este archivo, y, abro un nuevo libro.

Vamos a la pestaña datos, dentro del grupo obtener y transformar, desplegamos obtener datos, y, seleccionamos desde un libro de Excel.

Seleccionamos nuestro archivo, y, hacemos clic en abrir.

Aparece la ventana del navegador, donde vemos las tres hojas, hacemos clic en seleccionar varios elementos.

Marcamos las tres hojas.

Clic en transformar datos.

Vemos que aparecen filas NULL.

Pues desde la pestaña de inicio, desplegamos quitar filas, y, seleccionamos quitar filas en blanco.

Lo hacemos en las demás consultas.

Lo siguiente que vamos a hacer es anexar las tres consultas en una nueva.

Para ello, desde la pestaña de inicio, desplegamos anexar consultas, y, elegimos anexar consultas para crear una nueva.

En la ventana que se abre, marcamos tres o más tablas.

En la ventana de la derecha, llamada tablas para anexar, aparece la consulta que teníamos seleccionada, pues, desde la ventana tablas disponibles, marcamos cada consulta y hacemos clic en agregar.

Clic en aceptar.

Le cambiamos el nombre, y, la llamamos, por ejemplo, Todos.

Vemos que después de cada nombre aparecen valores nulos.

Pues vamos a rellenarlo con cada nombre.

Para ello, hacemos clic con el botón alternativo del ratón sobre la columna 1.

Desplegamos rellenar, y, seleccionamos abajo.

Ya lo tenemos, vemos también que las fechas están en formato de fecha y hora.

Cambiamos el nombre de Column1, por, Nombre.

Teniendo la columna nombres seleccionada, si vamos a la pestaña de transformar, y, desplegamos hora, vemos que aparecen atenuadas.

Esto es, porque para que estén habilitadas, debe de estar seleccionada una columna de fecha y hora.

Vamos a añadir una columna, para ello, vamos a la pestaña agregar columna.

Si despliego hora, puedo traerme solo la hora, inicio de hora, o, final de la hora, voy a seleccionar hora, teniendo seleccionada la columna de inicio.

Vemos la hora.

También, podría traerme minuto, o, segundo.

Elimino el paso de insertar hora.

Pero lo que quiero es la diferencia de horas para cada fecha.

Bien, si tengo seleccionado una sola columna de fechas, voy a la pestaña de agregar columna, y, despliego hora, puedo ver como la opción de resta no está disponible.

Esto es debido a que se necesitan dos columnas para poder realizar una resta.

Pues seleccionamos la columna de fin, dejamos pulsado CTRL y seleccionamos la columna de inicio.

Ahora, desplegamos hora, y, seleccionamos resta.

Vemos la resta de las horas.

Teniendo la columna resta seleccionada, vamos a la pestaña transformar.

Desplegamos duración, y, elegimos horas, para ver solo las horas.

Agregamos una nueva columna para extraer el día de la semana de inicio, para ello, vamos a la pestaña de transformar, desplegamos fecha, desplegamos día, y, seleccionamos nombre del día.

Con esto ya lo tendríamos.

Desplegamos cerrar y cargar, y, seleccionamos cerrar y cargar en, desde la pestaña de inicio.

Se abre la ventana, importar datos, marcamos, crear únicamente la conexión, y, aceptamos.

Aparecen las tablas en la parte derecha.

Solo vamos a cargar la tabla anexada.

Vamos a la parte derecha, a la ventana llamada consultas y conexiones.

Hacemos clic con el botón alternativo del ratón sobre la consulta anexada.

Elegimos cargar en.

Se abre la ventana, importar datos, marcamos tabla, y, aceptamos.

Ya tenemos nuestra tabla cargada en Excel.

Si se añaden nuevas fechas, solo tenemos que actualizar, o, refrescar.

Lo siguiente va a ser crear una tabla dinámica, para ver los totales por persona, pero desde la conexión.

Para ello, vamos a la pestaña insertar.

Desplegamos tabla dinámica, y, elegimos desde datos externos de origen.

En la ventana que se abre, hacemos clic en elegir conexión.

De nuevo, en la ventana que se abre, seleccionamos nuestra conexión, que es Todos.

Clic en abrir.

Marcamos hoja de cálculo existente.

La ponemos al lado de nuestro modelo.

Aceptamos.

Podemos ver a la derecha en la zona de campos, los campos de la tabla todos.

Nos llevamos a filas, el campo nombre, y, a valores el campo resta.

Podemos ver los totales de diferencia de horas para cada persona.

Con esto, ya hemos terminado nuestro ejemplo.