¿Clasificación ABC en PowerQuery? Sí, es posible

Power Query no tiene scan ni buscar XS, no pasa nada. Hoy te voy a enseñar cómo hacer una clasificación ABC de clientes usando solo Power Query, sin fórmulas de Excel, sin macros, sin complementos. Vamos a ir construyendo paso a paso un ranking de ventas, vamos a calcular el acumulado por filas y luego vamos a asignar las categorías ABC simulando un buscar X. Y todo esto con código M. Lo mejor es que vas a entender cómo funciona realmente por dentro Power Query y al final, además, vas a tener una tabla de ventas con la categoría ABC preparada para usar en tus informes y todo esto de una forma automatizada.

Vale, pues ya tenemos aquí el ejercicio que utilizamos para hacer la versión Excel de la clasificación ABC. Y en este caso teníamos aquí solucionado pues utilizando la función scan y la función buscar XS, pero lo que queremos hacer ahora es lo mismo, pero utilizando Power Query. Por lo tanto, vamos a esta tabla. Tenemos una tabla con ventas. Vamos a dejar la categoría del anterior ejercicio para que lo tengamos como referencia y comprobamos que el resultado es el que queremos. Y recordar que aquí teníamos una tabla donde indicábamos a partir de qué porcentaje asignábamos la etiqueta A, B o C.

Empezamos metiendo la tabla que tenemos dentro de Power Query. Recordar que como ya tenemos un formato de tabla, aquí tenemos la opción dentro de datos, una tabla o rango. Le damos aquí y nos va a cargar la información de esta tabla. Ya la tenemos aquí cargada. Nos ha puesto las columnas, no nos identifica el tipo de dato que es, por lo tanto se lo vamos a poner. Vamos a hacer que los clientes son texto, en este caso pues el producto texto y las ventas vamos a decir en este caso pues que sea un número decimal.

Siguiente paso. Lo primero que tenemos que hacer es calcular un ranking. Vamos a duplicar esta consulta que teníamos. La duplicamos, la vamos a llamar ranking y la forma de hacer un ranking es bastante sencilla. Directamente vamos a la opción de agrupar. Vamos a hacer la agrupación por clientes. Le damos aquí cliente. Queremos que nos sume las ventas. Vamos a ponerle el nombre de la columna ventas y suma de la columna ventas.

Tenemos ya una lista de clientes y ventas. ¿Qué nos interesa para hacer un ranking que estén ordenadas? Para eso, marcando la columna en la cual que tenemos los importes, le vamos a dar a la opción de que nos ordene, en este caso de Z a A, orden descendente, para tener ya los clientes ordenados por el total de ventas. Y una vez ya tenemos esta lista, lo único que vamos a tener que hacer es añadirles una columna nueva. Vamos a agregar columna, que sea una columna de índice. Recordar que una columna de índice nos añade una nueva columna añadiendo un secuencial. Y podemos elegir este secuencial desde dónde empieza. Pues vamos a decir que empiece desde el uno.

Ahora vendría el segundo caso, que es que queremos hacer sobre esta evolución que nos vaya poniendo fila a fila el acumulado hasta esa posición, porque el cálculo ABC requiere eso, que vayamos acumulando el porcentaje de venta que representan los clientes. Recordar que no existe en Power Query la función scan, que es la que nos hacía este cálculo, pero sí que vamos a poder añadir una nueva columna personalizada llamada acumulado.

Hay una función dentro de Power Query que lo que nos permite es seleccionar un número de elementos de una lista. Cuando yo pongo la referencia al paso anterior indicando la columna ventas, me trae toda la lista de ventas. Luego uso List.FirstN para limitar el número de elementos de esta lista según el valor de la columna índice. Para cada fila me va a traer el número de elementos que indique la columna índice.

Como yo lo que quiero es que me vaya poniendo el acumulado, es decir, que me haga la suma de todos estos valores, simplemente voy a usar List.Sum. Le damos al okay y ya me devuelve la suma de esa lista que ha ido calculando en función del campo índice y me va saliendo el acumulado.

Ya hemos conseguido tener un acumulado de valores totales, pero yo realmente lo que voy a necesitar ahora es que me calcule qué porcentaje este acumulador representa sobre el total de ventas. Lo que hago es dividir por List.Sum de toda la columna ventas del paso anterior. De esta forma cada valor ahora se divide por el total de la suma de todos los valores. Me transforma estos datos que eran absolutos en unos datos porcentuales incrementales y que además acaban en la última fila con un valor uno porque es cuando ya tenemos todas las ventas acumuladas, 100%.

Llegado a este punto, el siguiente paso es conseguir que esa tabla que teníamos de clasificación ABC, en la cual simplemente marcábamos dónde empezaba cada tramo, poderla mapear en función de estos porcentajes. Power Query no tiene buscar XS que nos haga esa búsqueda aproximada, pero tiene solución.

Nos metemos dentro de la tabla de categorías y lo que vamos a hacer es para cada categoría ir a buscar cuál es el elemento mayor que cumple con la condición de ser menor o igual que el porcentaje que hemos puesto como referencia. Añadimos una columna personalizada llamada elemento. Usamos Table.SelectRows sobre la tabla ranking, con la condición de que el acumulado sea menor o igual que el porcentaje.

Aquí es importante entender cómo piensa Power Query a la hora de gestionar el alcance de cada trozo de código. Cuando tenemos un each dentro de otro each, el segundo está en el ámbito de la tabla ranking y no encuentra el campo porcentaje de la tabla categorías. La solución es usar let para sacar ese valor fuera y asignarle un nombre de variable (límite). Así cuando aplicamos la función SelectRows sobre la tabla ranking, usamos la variable límite en vez de la referencia directa al campo porcentaje.

De toda esa tabla filtrada, me quedo solo con la columna índice y aplico List.Max para obtener el último valor que coincide, es decir, el índice del último cliente que cumple cada condición.

Luego hacemos un join. Combinamos la tabla de ranking con la tabla categorías usando el índice como clave. Hacemos combinación externa izquierda. Para aquellos elementos que implican cambio de categoría, nos marca qué categoría aplica desde ese momento. Los valores nulos los rellenamos con la función rellenar hacia arriba de Power Query.

Finalmente creamos la versión final combinando la tabla de ventas con la tabla de asignación por cliente. Hacemos inner join porque todos los clientes tienen una asignación. Expandimos seleccionando solamente la categoría y ya tenemos nuestra asignación que coincide línea a línea con la que teníamos precalculada en Excel.