jueves, 17 de junio de 2021

Power Query: Top 3 ventas por cliente

Veremos hoy un clásico del análisis de ventas: Listado del top 3 de ventas por cada cliente... resuelto con Power Query.
Partiremos de un sencillo listado (que ya he ordenado para verificar los resultados obtenidos):
Power Query: Top 3 ventas por cliente


Notemos algo que en ocasiones pasamos por alto.. en uno de los clientes aparece un importe repetido (en segunda y tercera posición). Obviamente, en mi opinión, ambas facturas de ventas deberán aparecer en nuestro listado.

Así pues cargaremos la tabla de ventas 'TblVENTAS' al editor de Power Query, y desde ahí entraremos al Editor avanzado donde escribiremos:
let
    Origen = Excel.CurrentWorkbook(){[Name="TblVENTAS"]}[Content],
    GrupoCliente = Table.Group(Origen, 
            {"Cliente"}, 
            { {"Sum3", each List.Sum( List.FirstN (List.Sort([Total],Order.Descending),3) )},
               {"Top3", each Text.Combine(List.Transform(List.FirstN (List.Sort([Total],Order.Descending),3),each Number.ToText(_) ),", ")} }),
    
    
    DividirColumna = Table.SplitColumn(GrupoCliente, "Top3", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Top3.1", "Top3.2", "Top3.3"}),
    TipoCambiado = Table.TransformColumnTypes(DividirColumna,{{"Top3.1", Int64.Type}, {"Top3.2", Int64.Type}, {"Top3.3", Int64.Type}})
in
    TipoCambiado

Power Query: Top 3 ventas por cliente


Lo relevante del ejemplo es cómo con la función M: Table.Group conseguimos nuestros listados
Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table
agrupamos sobre el campo 'Cliente' e incorporamos dos nuevas 'columnas agregadas': 'Sum3' y 'Top3'.

La primera 'Sum3' responde a una secuencia de funciones M anidadas, donde primero ordenamos en descendente la lista de importes 'Total' de cada cliente (con List.Sort), para luego recuperar los tres primeros con List.FirstN.
Esta combinación sería la equivalente de DAX: TOPN.
Finalmente, para 'Sum3' aplicamos List.Sum para acumular los tres 'totales' resultantes... para cada cliente.

Para la otra columna agregada 'Top3' a partir de la base anterior (List.FirstN sobre List.Sort), y con el fin de concatenar los tres 'totales' obtenidos, le aplicaremos una función List.Transform para convertir en texto cada importe, y poder unirlos con Text.Combine.

Para acabar dividiremos en tres columnas esta combinación y aplicaremos un cambio en el tipo de datos de éstas.

El resultado es el esperado:
Power Query: Top 3 ventas por cliente

martes, 15 de junio de 2021

Extraer valor numérico

Hace algunos días, durante una formación, un asistente me preguntaba por un problema que le surgía muchas veces al recuperar valores numéricos de cierto lugar... y es que esos números cada vez se importaban de distintas maneras, con distintos separadores de miles y/o decimales (coma o punto), mezclado con caracteres no numéricos, espacios en blanco, símbolos de monedas, etc...
Extraer valor numérico
Aquí explico una versión mejorada de la que le aporte inicialmente...

La fórmula buscada sería:
=LET(dato;B2;
calculo1;UNIRCADENAS("";VERDADERO; SI(ESNUMERO(--EXTRAE(dato;SECUENCIA(1;LARGO(dato));1))+(EXTRAE(dato;SECUENCIA(1;LARGO(dato));1)=".")+(EXTRAE(dato;SECUENCIA(1;LARGO(dato));1)=",");EXTRAE(dato;SECUENCIA(1;LARGO(dato));1);""));

Separadores;UNIRCADENAS("";VERDADERO;UNICOS(SI(NO(ESNUMERO(--EXTRAE(calculo1;SECUENCIA(1;LARGO(calculo1));1)));EXTRAE(calculo1;SECUENCIA(1;LARGO(calculo1));1);"");1));

negativo;SI.ERROR(SI(ENCONTRAR("-";dato)>0;"-";"");"");
separador1;EXTRAE(Separadores;2;1);
separador2;EXTRAE(Separadores;1;1);
SI.CONJUNTO(LARGO(Separadores)=0;VALOR((negativo&calculo1));
LARGO(Separadores)=1;VALOR.NUMERO((negativo&calculo1);separador2);
LARGO(Separadores)=2;VALOR.NUMERO((negativo&calculo1);separador1;separador2) ) )

Extraer valor numérico


Bajo ciertas condiciones respecto a los separadores decimales y de miles (indistintamente coma y/o punto) la fórmula parece responder adecuadamente, incluso para números negativos...
Desglosamos el paso a paso:
En el primer paso
calculo1;UNIRCADENAS("";VERDADERO; SI(ESNUMERO(--EXTRAE(dato;SECUENCIA(1;LARGO(dato));1))+(EXTRAE(dato;SECUENCIA(1;LARGO(dato));1)=".")+(EXTRAE(dato;SECUENCIA(1;LARGO(dato));1)=",");EXTRAE(dato;SECUENCIA(1;LARGO(dato));1);""));
La función recupera cualquier valor numérico de la cadena de texto dada, así como los caracteres coma y punto.
Nota la condición triple dada en el condicional para quedarnos con esos caracteres:
ESNUMERO(--EXTRAE(dato;SECUENCIA(1;LARGO(dato));1))+(EXTRAE(dato;SECUENCIA(1;LARGO(dato));1)=".")+(EXTRAE(dato;SECUENCIA(1;LARGO(dato));1)=",")
Dado como suma para replicar el operador lógico O

En el segundo paso recuperamos únicamente los separadores de miles y decimales, de manera única:
Separadores;UNIRCADENAS("";VERDADERO;UNICOS(SI(NO(ESNUMERO(--EXTRAE(calculo1;SECUENCIA(1;LARGO(calculo1));1)));EXTRAE(calculo1;SECUENCIA(1;LARGO(calculo1));1);"");1));

Aplicamos la función UNICOS para obtener una cadena de dos caracteres... del orden en el que aparezcan dependerá de cómo entendemos dichos separadores:
El primero que aparezca será el separador de miles
El segundo el decimal

En los siguientes pasos recuperamos posibles separadores y signo negativo:
negativo;SI.ERROR(SI(ENCONTRAR("-";dato)>0;"-";"");"");
separador1;EXTRAE(Separadores;2;1);
separador2;EXTRAE(Separadores;1;1);

Para finalmente aplicar al valor obtenido la función VALOR o VALOR.NUMERO según el número de separadores obtenidos: valor que solo debería ser 0,1 ó 2 (esto es, ningún separador; uno solo que entenderemos que será el decimal!!!; o dos separadores con el orden establecido: primero decimal y segundo de miles):
SI.CONJUNTO(LARGO(Separadores)=0;VALOR((negativo&calculo1));
LARGO(Separadores)=1;VALOR.NUMERO((negativo&calculo1);separador2);
LARGO(Separadores)=2;VALOR.NUMERO((negativo&calculo1);separador1;separador2) ) )

Extraer valor numérico
En este caso la función SI.CONJUNTO es la encargada de evaluar nuestros criterios...

Solo quedaría aplicar el formato deseado al ya número conseguido...
Nota final: obviamente se requiere al menos un valor numérico en la cadena de texto. ;-)

viernes, 11 de junio de 2021

Power Automate Añadiendo datos en Excel

Continuaremos estudiando algo más de las posibilidades de Power Automate junto a Excel.
En este artículo veremos cómo poder ejecutar un flujo desde Power Automate que:
1- identifique ciertos archivos de Excel en determinadas carpetas/subcarpetas,
2- una vez ubicados, abriremos dichos ficheros
3- identificaremos la última fila con datos de una de las hojas concreta (llamada 'ES') de estos archivos
4- y finalmente incorporaremos una función de Excel estándar (un SUMAR.SI en nuestro ejemplo)

En definitiva interactuaremos de forma completa con nuestras hojas de cálculo!!.

Condición de partida es que los distintos ficheros sobre los que trabajar tengan una hoja llamada 'ES', con datos en las columnas A y B (códigos e importes de ventas):
Power Automate Añadiendo datos en Excel

La estructura de la carpeta es la siguiente:
Power Automate Añadiendo datos en Excel


Obviamente cada fichero tiene un número distintos de filas!!.

Comenzaremos abriendo Power Automate Desktop, y accederemos a Crear flujo, donde empezaremos a trabajar.

Power Automate Añadiendo datos en Excel


En primer lugar crearemos una Variable de entrada que nos identifique en qué carpeta localizar nuestros ficheros de Excel.
Al crear esta variable nos preguntará dónde está la carpeta sobre la que trabajar...
Así pues desde el panel de la derecha de Variables de entrada/salida añadiremos una de entrada con la configuración siguiente:
Power Automate Añadiendo datos en Excel


Con la variable creada, podemos añadir el primer paso del flujo.
Será desde el grupo de Acciones de Carpeta: Obtener archivos de la carpeta, lo que generará una lista de ficheros de tipo .xlsx
Power Automate Añadiendo datos en Excel


El siguiente paso consiste en añadir un bucle For each desde el grupo de acciones de Bucles, que recorrerá cada elemento de la lista de archivos anterior...
Power Automate Añadiendo datos en Excel


Dentro de nuestro bucle incorporaremos una secuencia de acciones de Excel como:
- Iniciar Excel: abrirá el Excel indicado como elemento actual en el bucle que estamos
Power Automate Añadiendo datos en Excel


- Establecer la hoja de cálculo de Excel activa: Identificamos cómo se llama la hoja sobre la que trabajar del fichero recién abierto... en nuestro caso 'ES'
Power Automate Añadiendo datos en Excel


- Obtener la primera columna o fila libre de la hoja de cálculo de Excel. Donde obtenemos dos variables.. una la primera fila y otra para la primera columna de la hoja activa anterior...
Power Automate Añadiendo datos en Excel


- Escribir en la hoja de cálculo de Excel. Con la fila libre identificada, procedemos a añadir una fórmula SUMAR.SI que opere sobre los datos originales de cada libro.
Queremos añadir dinámicamente una suma condicionada que acumule los códigos 'a'.
Importante!!, hay que introducir la fórmula en inglés y con los separadores US, i.e., comas como separadores de argumentos.
Power Automate Añadiendo datos en Excel

Fíjate en la fórmula variable que hemos introducido:
=SUMIF(A1:A%FirstFreeRow - 1%,"=a",B1:B%FirstFreeRow - 1%)
donde los rangos los aplicamos dinámicamente incorporando la variable 'FirstFreeRow' (primera fila libre), pero restándole 1 para llegar a la fila anterior: %FirstFreeRow - 1%
Igualmente queda definida los parámetros de la celda destino de nuestra fórmula...

- Cerrar Excel: y tras añadir la fórmula, cerramos el fichero donde además indicamos que antes de cerrar se guarden los cambios.
Power Automate Añadiendo datos en Excel


Finalizado el flujo lo guardamos y ejecutamos. Puedes presionar F5, o el 'triángulo' de ejecutar...
Podrás ver la iteración por los distintos pasos del flujo, y finalmente verificar en los ficheros como se ha introducido nuestra fórmula:
Power Automate Añadiendo datos en Excel


Trabajo bien hecho y sin necesidad de códigos complejos!!.
Bien por Power Automate!!