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