Análisis ABC con Power Query

Wikipedia: Análisis ABC

Luego de un increíble primer taller de Power Query en español, le queremos brindar más contenido a nuestros asistentes que puedan poner en práctica (y entender) inmediatamente. Probablemente hayas visto anteriormente un patrón de la clasificación ABC aquí, pero esta vez veremos este análisis implementado con solo el uso de código M en Power Query. ¿Por qué? te podrías preguntar. No es solo una sencilla y rápida solución, en el caso que no desees utilizar Power Pivot, pero también tiene algunas ventajas importantes que pueden crear soluciones verdaderamente únicas que podrían tomar más tiempo implementar en Power Pivot o podrían terminar añadiendo complejidad innecesaria a un ya complejo modelo de datos. Demos un paso en la solución y Averigüemos por qué esto es un gran avance para todos los usuarios de Excel e incluso los usuarios avanzados de Power Pivot.

Conceptos

• Accesando datos de una base de datos SQL Server (en Azure)
• Utilizando filtros de fechas personalizados (mostrar últimos x días/meses/años)
• Obteniendo valores y tablas de tablas relacionadas
• Agrupando filas
• Utilizando Listas
• Porqué podrías considerar el uso de List.Buffer() para listas
• Totales acumulados con Power Query
  • Utilizando una columna de Índice o
  • La función List.Select
• Lógica condicional simple con if

Nuestra fuente de datos

Primero, necesitamos decirte que utilizaremos datos de una base de datos SQL Server en Azure. El acceso a esta base de datos es gratuito para cada uno de nuestros asistentes, por lo que si has asistido a nuestro taller puedes tener acceso a este ejemplo. Dentro de este servidor tenemos múltiples bases de datos, pero la que utilizaremos aquí es la base de datos de AdventureWorks.

Paso 1: Obtén los datos

En la cinta de Power Query , selecciona From Azure y luego selecciona From Azure SQL Database. Ingresa los datos del servidor y base de datos para poder conectarnos a la fuente.

Se le solicitará que introduzca sus credenciales con el método de autenticación correcta. Una vez esto sucede, seleccionaramos la tabla SalesOrderDetail de la lista o simplemente utilizamos la ventana de búsqueda para encontrarla y luego damos clic en Editar para ver la tabla y esta lucirá de la siguiente manera:  Lo que necesitamos hacer primero es movernos hacia la derecha en la tabla y encontrar las columnas con valores Value.

Una vez la encontremos, podemos expandir dichas columnas de la siguiente manera:

• En la columna Sales.SalesOrderHeader:
• En la columna Sales.SpecialOfferProduct:

Una vez esto suceda, nuestro resultado nos brindará 2 columnas. Una con la fecha de la orden y la otra con los datos relacionados del producto vendido:

Sigamos y vamos a expandir la columna Product.Product de la siguiente forma:

Y una vez hallamos terminado con las operaciones Table.ColumnExpand el resultado serán 2 nuevas columnas: OrderDate y ProductNumber.

Paso 2: Filtrar los datos utilizando Filtros de Fecha

Una de la características relativamente nueva de Power Query es la opción de filtrar por Fecha/Tiempo:

En nuestro escenario, queremos agregar un filtro In the Previous..  donde podamos siempre obtener el Análisis ABC basado en los datos del último mes, año o tal vez un número específico de días. Esta nueva ventana de diálogo es también muy amigable al usuario!

Usted podría incluso tener algunos cálculos aún más complejos en el que tiene que hacer el filtrado en diferentes niveles como:

• Año-A-Fecha
• Este mes
• Esta semana
• y otros

Mediante el uso de esta característica me estoy asegurando de que siempre tenga un escenario más dinámico en el que se hará el Análisis ABC contra, digamos, las últimas 52 semanas de datos de ventas o tal vez simplemente los datos del último año y podríamos compararlo contra los datos del año actual (que potencialmente podrían llevarnos unos simples clics, una vez que terminemos esta solución si quisiéramos!)

Paso 3: Resumir los Datos

Hemos llegado a un punto donde podemos simplemente agrupar los datos por parte de la columna ProductNumber y simplemente tener el total de ventas para cada producto. Podemos hacer esto simplemente al seleccionar la función Group By (Agrupar por) de la cinta Transformar que nos brindará esta nueva ventana con el siguiente diálogo:

Y el resultado de dicha operación nos brinda la siguiente tabla:

Paso 4: Crea una Lista de todos los valores a ser considerados

Simplemente agregamos un paso personalizado y agregamos el siguiente código en ella (recuerda que puede hacer clic derecho en la columna y añadir un nuevo paso sobre la base de esa columna como una lista):

• = List.Buffer(#»Grouped Rows»[Total Sales])

Esto crea una Lista con los valores encontrados en la columna Total Sales del paso anterior (Grouped Rows). Utilizamos List.Buffer para estabilidad de la lista y optimizar el rendimiento ya que utilizaremos esta lista luego. Podrías considerar usar List.Buffer en momentos como este donde necesitas utilizar una lista larga muchas veces o para hacer cálculos algo complejos pues el no usar esta función (List.Buffer) podría afectarnos en rendimiento. También he notado que todos los pasos antes del paso de Agrupar por (Grouping By) no utilizar el query folding o plegado de consultas, así que todas las operaciones luego de dicho paso son hechas localmente. El resultado de la operación anterior nos brinda una lista como la siguiente:

Ahora que hemos terminado la creación de esa lista, tenemos que volver al paso en donde estábamos.

Da un clic en el ícono FX dentro de la barra de fórmulas e introduce el nombre del paso anterior como se muestra en la siguiente imagen:

Paso 5: Agregar una Columna de Acumulado

Hay muchas maneras de crear esta columna acumulativa, pero las 2 más eficientes son:

• Utilizando una columna de índice como parámetro combinado con funciones como List.Range, List.FirstN, List.Generate y luego agregar la lista resultante como funciones como List.Sum y List.Accumulate.
  • A menudo, esto requiere que hagas una clasificación antes de la etapa anterior para que puedas obtener los datos dispuestos en una forma descendente o ascendente según sea necesario y que a menudo puede añadir algunos pasos o complejidad innecesarios.
• Utilizando List.Select
  • La forma más sencilla y que replica lo que Marco y Alberto hicieron con la función EARLIER en DAX

En nuestro caso, iremos con List.Select y agregamos una nueva columna personalizada como la siguiente:

Mil gracias a Curt Hagenlocher del equipo de Power Query MSFT por ayudarme a descifrar las últimas piezas del código. Lo que hace en esencia es básicamente tomar una lista (en nuestro caso es la lista que hemos creado antes con el paso MyList) y sólo traer los elementos que cumplan determinados criterios. El segundo argumento es realmente una función donde x es el valor encontrado en cada elemento dentro de la lista y [Total Sales] es el valor actuando como un criterio.

Así que, en resumen, básicamente estamos calculando una nueva lista mediante una operación que se hace fila por fila base en nuestra tabla actual que es el resultado de filtrar la lista MyList donde el valor de la columna [Total Sales] debe ser menor o igual que el de los valores en MyList. Una vez que tenemos la lista de salida, simplemente utilizamos la función List.Sum para sumarlo y el resultado se puede ver en la imagen anterior. Como se puede ver, no hemos ordenado esa tabla y ya vemos algunos resultados prometedores.

Paso 6: Agregar la columan de  % y de Clasificación

Vamos a dejar la clasificación para el final. Por ahora, vamos a centrarnos en añadir las columnas necesarias mediante las siguientes fórmulas:

Después de añadir esas 2 columnas personalizadas, nuestra tabla debería tener este aspecto:

Paso 7: Ordenando y Redondeando

Ahora podemos ceder y ordenar la tabla como queremos, además de corregir los decimales redondeando los números. El resultado final debe ser similar a esto:

Otros pasos que podríamos tomar….

• Potencialmente podríamos añadir más datos, relacionados con el producto, mediante la fusión de las tablas
• Podríamos construir múltiples consultas, 1 para cada año o tiempo deseado, y compararlos
• Podríamos cargar esta tabla directamente a nuestro modelo de Datos
• Podríamos agrupar por Subcategory, Category u otro atributo y hacer el Análisis ABC a ese nivel

El código

Para las personas que les encanta mirar el código 🙂

let
    Source = Sql.Database(server, "AdventureWorks2012"),
    Sales_SalesOrderDetail = Source{[Schema="Sales",Item="SalesOrderDetail"]}[Data],
    #"Expanded Sales.SalesOrderHeader" = Table.ExpandRecordColumn(Sales_SalesOrderDetail, "Sales.SalesOrderHeader", {"OrderDate"}, {"OrderDate"}),
    #"Expanded Sales.SpecialOfferProduct" = Table.ExpandRecordColumn(#"Expanded Sales.SalesOrderHeader", "Sales.SpecialOfferProduct", {"Production.Product"}, {"Production.Product"}),
    #"Expanded Production.Product" = Table.ExpandRecordColumn(#"Expanded Sales.SpecialOfferProduct", "Production.Product", {"ProductNumber"}, {"ProductNumber"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Production.Product", each Date.IsInPreviousNYears([OrderDate], 7)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ProductNumber"}, {{"Total Sales", each List.Sum([LineTotal]), type number}}),
    MyList = List.Buffer(#"Grouped Rows"[Total Sales]),
    Custom1 = #"Grouped Rows",
    #"Added Custom" = Table.AddColumn(Custom1, "Acumulative", each List.Sum( List.Select(MyList, (x) => x>=[Total Sales]))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Acumulative", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Percentage", each [Acumulative] / List.Sum(MyList)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Classification", each if [Percentage] < 0.7 then "A" else
         if [Percentage] < 0.9 then "B" else
"C"),
    #"Sorted Rows" = Table.Sort(#"Added Custom2",{{"Total Sales", Order.Descending}}),
    #"Rounded Off" = Table.TransformColumns(#"Sorted Rows",{{"Total Sales", each Number.Round(_, 2)}, {"Acumulative", each Number.Round(_, 2)}}),
    #"Rounded Off1" = Table.TransformColumns(#"Rounded Off",{{"Percentage", each Number.Round(_, 3)}})
   in
    #"Rounded Off1"

Conclusión

Power Query no es sólo una herramienta para quitar o cambiar el nombre de las columnas, anular la dinamización de columnas, agrupar, anexar o fusionar las tablas – es una verdadera herramienta de ETL que se encuentra dentro del ambiente que ya conoces y amas –Excel. Dominar esta herramienta es mucho más sencilla que dominar un lenguaje como VBA inclusive, obtienes más beneficios de Power Query ya que fue diseñada para ser una herramienta asombrosa de ETL. Los escenarios que puedes resolver con esta herramienta son muchísimos.