lunes, junio 19, 2017

Cálculo de lapsos con Power Query

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".

martes, junio 13, 2017

Cálculo de fecha de finalización de curso

Supongamos que programamos un curso de programación dividido en 10 encuentros/clases. Las clases se dictarán todos los lunes, miércoles y viernes. La pregunta es: ¿cuál es la fecha del último encuentro?

Podemos resolver este problema con facilidad usando la función DIA.LAB.INTL (Excel 2010 y versiones posteriores) pero tomando en cuenta un parámetro oscuramente explicado en la ayuda en línea de la función.


Empezamos por montar nuestro modelo


En las columnas D a H marcamos con el 0 (cero) los días en que se dictarán las clases y con 1 los días de la semana en los que no se dictarán las clases del curso. Ésto parece ir contra la intuición ya que, siendo usuarios consuetudinarios de Excel, asociamos el 0 con el valor FALSO y el 1 con el valor VERDADERO. Pero, como veremos más adelante, la función DIA.LAB.INTL requiere que usemos estos valores.
Como podemos ver, el curso de Programación comprende 10 encuentros (columna C) y se dicta los días Martes y Jueves.

Ahora agregaremos la columna con el cálculo de la fecha de cierre del curso


La fórmula en la celda J3 es =DIA.LAB.INTL(I3,C3-1,CONCAT(D3:H3)&"11")

Antes de seguir adelante aclaremos que la función CONCAT está disponible solamente para los usuarios de Excel 2016 (en Office 365). Usuarios de versiones aneriores deberán usar la función CONCATENAR.

Expliquemos la fórmula



El primer argumento es la fecha inicial; el segundo es el número de días que en nuestro caso es el número de encuentros. El tercer argumento es un valor textual compuesto de 1 y 0 que indican cuales son los días laborales de la semana y cuales no. Este valor textual debe tener siempre siete símbolos (uno por cada día siguiendo el orden de los días de la semana). 
El "truco" en esta fórmula es definir sólo los días en que se dicta el curso como días laborales. 

Opcionalmente podemos agregar una lista de feriados, el cuarto argumento, en caso de ser necesario.

También podemos cambiar el formato de las fechas para que muestren el día de la semana 


Otra mejora posible es usae Formato Numérico Personalizado para mostrar "si" en lugar de 0 y "no" en lugar de 1



con esta definición



o mejor aún con Formato Condicional


definido de esta manera


martes, abril 11, 2017

Cálculo de tiempo acumulado con Power Query

La principal dificultad del usuario Excel experimentado al comenzar a trabajar con Power Query no es tanto acostumbrarse al nuevo entorno, comprender los nuevos objetos y el nuevo idioma (M) o dominar las nuevas funciones. El principal obstáculo a vencer es dejar de “pensar Excel”.


Supongamos (en mi caso más que suposición era parte de un proyecto) que tenemos una serie de mediciones en una tabla; en una columna tenemos el momento de la medición (fecha y hora) y en otra tabla el valor medido.

La tarea a realizar es calcular el tiempo acumulado transcurrido desde la primera medición hasta la última, para cada medición. Con Excel la tarea es sencilla: en la primer celda de la columna C de la tabla ponemos es fórmula =A2-$A$2 y la copiamos a lo largo de la columna


Sólo tenemos que asegurarnos de “anclar” el sustraendo (=A2-$A$2). 

Si es tan fácil hacerlo con fórmulas de Excel, ¿por qué hacerlo con Power Query? Hay muchos motivos (supongamos por ejemplo una tabla con dos millones de filas), pero en mi caso se trataba de un proyecto totalmente desarrollado con Power Query.

En Power Query no existe el concepto de celda tal como lo conocemos en Excel, por lo que no podemos replicar el cálculo que he mostrado más arriba. A continuación mostraré los pasos.

Empezamos por cargar la tabla de datos a la ventana de Power Query 

Luego selecccionamos el primer valor del campo Registro en la ventana del Power Query y aplicamos Drill Down


con lo que obtenemos esta situación


Como pueden apreciar, el menú en la cinta a cambiado a "Text Tools"; en la ventanilla de la propiedades cambiamos el nombre (Name) a "varComienzo" (la partículo "var" la agrego como método de identificar que se trata de una variable).
Terminamos el proceso cargando la consulta como "sólo conexión" (Home-Close and Load-Connection Only). Nuestra hoja se ve ahora así


Ahora vamos a cargar la tabla y vamos a agregar una columna que llamaremos "Comienzo"; esta columna contendrá el valor de la variable (fecha y hora de la primera medición). Luego creamos la columna "Tiempo Acumulado" restando la columna Registro de la columna Comienzo. Finalmente eliminamos la columna Comienzo, que ya no necesitamos, y cargamos la tabla a una hoja de Excel. Todo el proceso lo muestro en este video.



Un detalle a tomar en cuenta es el tipo de variable que usamos para el tiempo acumulado: "Duration".