Comencé a
publicar posts sobre Power Query hace casi cuatro años (este fue mi primer post sobre el tema). Desde entonces no sólo he publicado artículos sobre
esta herramienta (indispensable para todo quien trabaje con masas de datos)
sino que también se ha convertido en mi herramienta principal. Puedo decir que
hoy en día la mayoría de las soluciones que desarrollo en Excel se basan, total
o parcialmente, en el Power Query.
Hoy voy a
mostrar como calcular lapsos, el tiempo transcurrido entre dos instantes,
usando el Power Query. Supongamos que tenemos esta serie de mediciones y queremos
saber cuánto tiempo ha transcurrido entre cada medición
Podemos
hacerlo usando fórmulas de Excel, por supuesto. Pero supongamos que tenemos una
gran cantidad de datos (en el caso real que traté había más de 50 mil
mediciones) y que tenemos que realizar otras transformaciones por lo que Power
Query será la herramienta ideal para el caso.
Como casi
todas las notas de este blog ésta está dirigidas al usuario medio o
medio-avanzado. Es decir, voy a prescindir de tecnicismos y pondré el énfasis
en los aspectos prácticos.
Observando
detenidamente el ejemplo podemos ver que hay dos tablas: una contiene una sola
fila con la fecha y hora del comienzo de las mediciones y la segunda con las
mediciones. Antes de crear las consultas consideremos lo siguiente: el cálculo
de cada lapso es el tiempo transcurrido entre una medición y la inmediata
anterior; pero el primer lapso es entre la primera medición de la tabla y el
punto inicial. Éste dato se encuentra en otra tabla. Para poder usar este dato
vamos a hacer lo siguiente:
Cargamos la
tabla del punto inicial a una consulta
Con un clic del mouse abrimos la opcíón "Rastrear..." (drill down)
con este resultado
En la ventanilla Propiedades de la consulta cambiamos el nombre a "Inicio" y cerramos la consulta con la opción "sólo crear conexión". Luego cargamos la tabla de mediciones, sobre la cual haremos todos los cálculos
Nótese los
diferentes iconos en las consultas, lo que nos indica que Power Query las está
tratando de forma distinta. De hecho, la segunda es una tabla (como lo indica
el icono) mientras que “Inicio” es una especia de variable (podemos cambiar el
valor en la hoja de Excel y el cambio se reflejará en la consulta).
Ahora vamos a editar la consulta de las mediciones donde haremos nuestras cálculos. Empezamos por agregar una columna Índice con base cero
Cambiamos el nombre del último paso a "AgregInd" (nemotécnica para Indice Agregado)
Ésto nos permite trabajar con más facilidad en los próximos pasos.
Ahora agregamos una columna personalizada con esta fórmula
con este resultado
Debemos prestar atención a estos detalles en nuestra fórmula
if [Índice]=0 then[Medicion]-Inicio else [Medicion]-AgregInd[Medicion]{[Índice]-1}
- usamos directamente Inicio para referirnos a la consulta que contiene el punto de partida, como si estuviéramos usando una variable (o constante a los efectos de la consulta);
- para referirnos a la fila anterior anteponemos el nombre del paso anterior (AgregInd) al nombre de la columna sobre la cual queremos operara (Medicion, en nuestro caso) y usamos la expresión Indice - 1 entre corchetes para indicar que queremos referirnos a la fila anterior (no voy a entrar aquí en explicaciones sobre los objetos de Power Query, tablas y listas).
Dos detalles par finalizar:
- eliminamos la columna Indice
- cambiamos el tipo de dato de la columna Lapso usando la opción Transformar - Detectar tippo de datos
Todo lo que nos queda por hacer es cargar la consulta a una tabla, si así lo queremos, o dejarla como "sólo conexión".
El archivo con el ejemplo se puede descargar aquí
