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

SI anidado con Power Query

Uno de los temas más frecuentes en las consultas que recibo es el de la función SI anidada. Es decir, todo tipo de ejercicios sobre como calcular un resultado bajo una serie de condiciones (por ejemplo, compras de hasta los 1000 pesos reciben un descuento del 5%; si superan los 5000, un descuento del 10%; compras de más de 10000, 7%, etc.). Estos ejercicios son muy populares en todo tipo de cursos Excel, inclusive en el sector académico, por algún motivo que escapa a mi modesto entendimiento. En lugar de complicarnos la vida armando una fórmula complicada , recordemos que Excel acepta hasta 64 niveles de SI en una fórmula, podemos usar una simple tabla y la función BUSCARV, tema que ya he tratado en esta prehistórica nota.

Siguiendo con el tenor de mis últimos posts, voy a mostrar cómo utilizar Power Query para solucionar cálculos con SI anidado con facilidad, sin dolores de cabeza y evitando, además, cargar nuestras hojas con muchas funciones SI.

Para nuestro ejemplo vamos usar esta tabla de ventas del año 2016 sobre la cual nos piden calcular las comisiones a pagar a los agentes. Por facturas superiores a 5000 les corresponde una comisión del 7.5%; por facturas que superen los 3000, 5%; facturas de más de 1500, 2.5% y las restantes sólo el 1%.

El primer problema con esta tabla es que las facturas aparecen en varias filas, una por cada producto. Por lo tanto tendremos que agrupar las ventas por factura.  En la época pre-Power Query lo hubiéramos hecho con una tabla dinámica, pero el Power Query nos ofrece otra alternativa: "Agrupar por" (Group by).

Empezamos por crear una conexión a la tabla (supongamos que se encuentra en una base de datos, no en una hoja de Excel) abriéndola en el editor de Power Query

De todas la columnas sólo necesitamos Agente, Nro. de Factura y Venta, pero no hace falta eliminar las restantes; Group by hará el trabajo por nosotros.

Abrimos el menú de Group By y hacemos las siguientes definiciones

Apretamos "Ok" y Power Query realiza la agrupación

Como puede apreciarse, nuestra tabla tiene ahora una fila por factura y agente con el total para cada factura.

Ahora vamos a calcular las comisiones agregando una columna condicional. Esta es una mejora agregada en una de las últimas actualizaciones del Power Query. Cuando activamos el menú de Add Column - Conditional Column, veeremos un formulario que nos permite crear todas las condiciones con facilidad

Elegimos la columna, el operador, el valor de la condición y el resultado para la primer condición; luego apretamos el botón "Add rule" para agregar las siguientes y finalmente ponemos el valor en la casilla "Otherwise" para la última condición (el resultado si todas las condiciones anteriores no se cumplen). Este es el resultado

Si observamos la nueva columna (mientras tanto lleva el nombre de "Custom"), veremos que los números están alineados a la izquierda. Esto nos indica que debemos transformarlos en números. Podemos hacerlo pulsando el "ABC123" en el ángulo izquierdo del encabezado

Ahora que los hemos convertido en números, podemos agregar una columna calculada con la comisión por factura

con este resultado

A esta altura de los acontecimientos podemos volcar los datos a una hoja de Excel, pero aquí vamos a hacer algo distinto. Vamos a guardar la tabla como conexión

Finalmente vamos a usar una tabla dinámica sobre esta conexión para crear nuestro reporte de comisiones. Empezamos con el menú Insertar-Tabla Dinámica con la opción "Utilice un fuente de datos externa"

Al apretar "Elegir conexión", la que acabamos de crear aparecerá en la parte superior del cuadro

Apretamos aceptar y veremos el familar cuadro de las tablas dinámicas

Todo lo que nos queda por hacer es arrastrar los campos requeridos a las áreas de filas, columnas y valores, según el reporte que queramos crear; por ejemplo

Nuevo catálogo de imágenes con Excel

Han pasado casi siete años desde que publiqué el último post sobre el tema de catálogos de imágenes con Excel. Este tema se encuentra entre los más leídos por mis lectores (aunque debo señalar que no recomiendo crear este tipo de aplicaciones con Excel).

En el post mencionado uso Vba (macros) para incrustar las imágenes guardadas en una carpeta en la hoja. La técnica consiste en guardar las direcciones de las imágenes en una tabla de Excel. En otra hoja el usuario introduce un texto que hace referencia a la dirección de la imagen y de esta manera, con el código, introducimos la imagen en la hoja de Excel. Esto tiene muchas ventajas pero presenta la dificultad de tener que actualizar la lista de referencia-dirección de la imagen.

Usando Power Query podemos crear una consulta que mantenga actualizada la lista de direcciones, en lugar de tener que usar todo tipo de códigos complicados.
Supongamos que queremos construir un modelo donde al ingresar el usuario el nombre de un país en la celda contigua aparece la bandera del país elegido. En nuestro ejemplo tenemos todos los archivos de las imágenes de las banderas en la carpeta “Banderas”

El proceso lo muestro en este video

Una vez creada la conexión, solo tenemos que apretar el botón Actualizar para mantener la tabla de referencias actualizada

El próximo paso es crear el nombre definido "rngPaisDireccion" que se refiere a la tabla completa, sin los encabezados

En otra hoja creamos esta tabla, con los encabezados y una fila vacía

Creamos un nombre definido que se refiere a todas las celdas de la columna Pais ("PicList"), que luego usaremos en el código

Como ven, ya hemos agregado dos botones para activar las macros. Una para insertar las banderas correspondientes a los nombres de los países que ingresemos en la columna A y otro para remover los países que ingresamos y las imágenes de sus banderas.

El código para insertar las banderas es

 Sub insert_pic()  
   Dim strFileName As String  
   Dim iTop As Integer  
   Dim rngCellPic As Range  
   'comprobar que se introdujeron paises  
   If WorksheetFunction.CountA(Range("PicList")) < 1 Then  
     MsgBox "No se anotaron paises", vbCritical  
     Exit Sub  
   End If  
   Application.ScreenUpdating = False  
   For Each rngCellPic In Range("PicList")  
   'defiinir alto de fila a 60 y centrar  
   With rngCellPic  
     .RowHeight = 60  
     .VerticalAlignment = xlCenter  
   End With  
   'introducir la bandera  
   strFileName = WorksheetFunction.VLookup(rngCellPic, Range("rngPaisDireccion"), 2, 0)  
   ActiveSheet.Shapes.AddPicture Filename:=strFileName, _  
       linktofile:=msoFalse, _  
       savewithdocument:=msoCTrue, _  
       Left:=rngCellPic.Offset(0, 1).Left + 15, _  
       Top:=rngCellPic.Offset(0, 1).Top + 5, _  
       Width:=50, Height:=50  
   Next rngCellPic  
   Application.ScreenUpdating = True  
 End Sub  

El código para limpiar todas las filas de la tabla es el siguiente

 Sub clean_all()  
   Dim rngCell As Range  
   Dim shpImage As Shape  
   Application.ScreenUpdating = False  
   'delete pictures  
   For Each shpImage In shList.Shapes  
     If shpImage.AlternativeText <> "NoDelete" Then shpImage.Delete  
   Next shpImage  
   For Each rngCell In Range("PicLIst")  
     rngCell.RowHeight = 14.25  
   Next rngCell  
   On Error Resume Next  
   Range("PicList").EntireRow.Delete  
   On Error GoTo 0  
   Application.ScreenUpdating = True  
 End Sub  

El modelo en funcionamiento