martes, 14 de julio de 2020

Alternativas a MAX.SI.CONJUNTO

Hace bastante tiempo que no escribo nada sobre funciones, y revisando material publicado me percaté que nunca escribí sobre la función MAX.SI.CONJUNTO (operativa desde la versión 2016 y +).
Esta función es muy interesante, dentro de la familia de las funciones .SI.CONJUNTO, ya que permite recuperar un valor máximo de entre distintos registros que verifiquen hasta 127 criterios... muy potente, sin duda!!.
Veamos un ejemplo:
Alternativas a MAX.SI.CONJUNTO

La función aplicada a este ejemplo que permite conocer la fecha mayor (más reciente) para un 'empleado' y un 'cliente' dado sería:
=MAX.SI.CONJUNTO(TblVisitas[Día];TblVisitas[Empleado];$F3;TblVisitas[Cliente];G$2)
función al uso, donde el primer argumento indica el rango de donde obtener el valor máximo, y el resto de argumentos alterna rango de criterio y criterio aplicado (ver funciones SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO o PROMEDIO.SI.CONJUNTO para mayor entendimiento).

La función es muy potente pero al no estar disponibles en versiones previas al 2016 nos da problemas de compatibilidad, que hoy veremos algunas formas de 'combatir'.

Una primera opción sería usar fórmulas matriciales (las clásicas CSE - ejecutadas presionando Ctrl Mayusc y Enter):
{=MAX(SI(TblVisitas[Empleado]=$F12;SI(TblVisitas[Cliente]=G$11;TblVisitas[Día])))}
donde hemos incluido tantos condicionales SI como criterios tengamos, para finalmente aplicar la función MAX.
Como inconveniente de este tipo de funciones tenemos el alto consumo de recursos que necesita para su recálculo.

Una alternativa diferente sería el uso de INDICE en su formato de referencia, donde al indicar como argumento de fila un cero 0, recuperamos un conjunto o matriz de valores...
=MAX(INDICE(TblVisitas[Día]*(TblVisitas[Empleado]=$F20)*(TblVisitas[Cliente]=G$19);0)) Además de forma interna evalúa las condiciones o criterios obtenidos del producto de rango condicionados.

Otra opción muy similar a la anterior vendría de la mano de SUMAPRODUCTO
=SUMAPRODUCTO(MAX((TblVisitas[Día]*(TblVisitas[Empleado]=$F28)*(TblVisitas[Cliente]=G$19))))
con una mecánica de cálculo casi idéntica a la anterior.

Y la última posibilidad que he trabajado responde al uso de las fórmulas desbordadas (disponibles a fecha de hoy a ciertos usuarios de 365):
=INDICE(ORDENAR(FILTRAR(TblVisitas[Día];(TblVisitas[Empleado]=$F36)*(TblVisitas[Cliente]=G$35);"");1;-1);1)
donde con la nuevas funciones básicamente se filtran por las condiciones necesarias de 'cliente' y 'empleado', para luego ordenar en sentido descendente... quedando la fecha mayor o más reciente en la primera celda/superior del rango desbordado.
Finalmente la funció INDICE recupera esa primera celda del rango desbordado obtenido.

Puedes ver en la imagen más arriba el resultado obtenido en cada caso.

Seguro habrá más formas y variantes, pero creo es un buen compendio de formulación avanzada ;-)

jueves, 9 de julio de 2020

Power Query: Repetir y numerar filas como n de N

Recientemente una lectora preguntaba la posibilidad de pasar de un listado donde se indicaban repeticione spor registro a un listado desglosado con tantas filas repitas y numeradas como se inidicase...
Fácil de entender viendo la imagen siguiente:
Power Query: Repetir y numerar filas como n de N

Vemos como pasamos de una sola fila donde se indican 5 repeticiones a cinco filas numeradas como 1 de 5, 2 de 5, ..., 5 de 5.. y así con cada registro.
La usuaria pedía una macro, pero pensé que Power Query podría ofrecer un resultado igual de válido... y así fue, y así lo voy a explicar. ;-)
Comenzamos cargando la tabla al Editor de consultas de Power Query, desde la Ficha Datos > grupo Obtener y transformar > botón Desde tabla o rango.
Ya en el editor empezaremos a incorporar etapas o pasos.
El primer paso es añadir una columna personalizada con la siguiente función, que emplea Table.Repeat:
Table.Repeat(
  		Table.FromRecords({
        	[Col1=1]
            }),
        [Repetición])

Power Query: Repetir y numerar filas como n de N

El resultado sería:
Power Query: Repetir y numerar filas como n de N
Donde vemos que Table.Repeat devuelve una tabla con tantas filas como se especifiquen en el segundo argumento.. en mi ejemplo el valor dado en el campo 'Repetición' Table.Repeat(table as table, count as number) as table
Este paso es básico, ya que nos permite repetir las filas tal como esperábamos...
En el siguiente paso expandiremos la columna recién añadida, y añadairemos una columna de índice:
Power Query: Repetir y numerar filas como n de N

Para la siguiente etapa debemos recordar cómo obtener un acumulado creciente condicionado con Power Query, para ello revisa esta entrada del blog
Así pues añadimos una nueva columna personalizada:
let Id=[Id],Índice=[Índice] in
        List.Sum(Table.SelectRows(ÍndiceAgregado, each [Índice]<=Índice and [Id]=Id )[Col1])

Power Query: Repetir y numerar filas como n de N

El resultado es un incremental basado en la anterior columna personalizada, obtenida con Table.Repeat, condicionada al campo 'Id' original...
Power Query: Repetir y numerar filas como n de N


Ya tenemos todo lo que necesitamos. La nueva columna 'Acum' que crece por cada registro, y el total para cada registro en el campo 'Repetición' de la tabla original.
Por tanto bastará seleccionar la columna 'Acum' y luego 'Repetición' y desde la ficha Transformar > Columna de texto > Combinar columnas, personalizando el separador por ' de '
Power Query: Repetir y numerar filas como n de N
Finalizamos eliminando columnas que ya no necesitamos... como 'Col1' e 'Índice'. LLegando al resultado mostrado al imagen del post.

El código completo visto desde el editor avanzado sería:
let
    Origen = Excel.CurrentWorkbook(){[Name="TblVentas"]}[Content],
    ColADD = Table.AddColumn(Origen, "Personalizado", each Table.Repeat(
                                                                Table.FromRecords({
                                                                    [Col1=1]
                                                                }),
                                                                [Repetición])),
    Expandido = Table.ExpandTableColumn(ColADD, "Personalizado", {"Col1"}, {"Col1"}),
    ÍndiceAgregado = Table.AddIndexColumn(Expandido, "Índice", 1, 1),

///
RunTotal = Table.AddColumn(ÍndiceAgregado, "Acum", each let Id=[Id],Índice=[Índice] in
        List.Sum(Table.SelectRows(ÍndiceAgregado, each [Índice]<=Índice and [Id]=Id )[Col1])),
    #"Columnas combinadas" = Table.CombineColumns(Table.TransformColumnTypes(RunTotal, {{"Acum", type text}, 
        {"Repetición", type text}}, "es-ES"),
        {"Acum", "Repetición"},
        Combiner.CombineTextByDelimiter(" de ", QuoteStyle.None),"n de N"),
    #"Columnas quitadas" = Table.RemoveColumns(#"Columnas combinadas",{"Col1", "Índice"})
in
    #"Columnas quitadas"

Power Query: Repetir y numerar filas como n de N
Ya podemos Cerrar y cargar en... nuestra consulta en nuestra hoja de cálculo destino.

martes, 7 de julio de 2020

Power Query: Renombrar pasos de la consulta

Hoy hablaremos de una recomendación al trabajar con nuestras consultas de Power Query, en especial cuando éstas tienen muchos pasos intermedios grabados... la recomendación del día es: Renombra los pasos
Imagina una consulta de este estilo
Power Query: Renombrar los pasos de la consulta
Lo normal es que la asignación de los nombres de los pasos se asignen automáticamente en el idioma elegido; esto puedes definirlo en Archivo > Opciones y configuración > Opciones de consulta > GLOBAL > Configuración regional > Pasos de la consulta
Power Query: Renombrar los pasos de la consulta

Lo 'malo' es que los nombres de los pasos son demasiado genéricos y poco descriptivos... de ahí la recomendación de Renombrar los pasos, lo cual facilitará futuras modificaciones y búsquedas dentro de la consulta.

El proceso de cambio es simple, basta hacer clic derecho sobre cada paso y seleccionar la opción Cambiar nombre, repitiendo la acción en todos los pasos que consideremos... lo interesante es que todo el código M generado se ajustará a este cambio...
Power Query: Renombrar los pasos de la consulta


La diferencia que aporta este trabajo es grande... ya que con un simple vistazo, tras renombrar los distintos pasos, podemos localizar etapas o pasos a modificar o consultar muy rapidamente...
Power Query: Renombrar los pasos de la consulta

Se ve en la imagen anterios como los nuevos nombres describen algo mejor la acción realizada, tal como buscábamos...

Obviamente esto se debería realizar a medida vamos grabando los pasos ;-)