jueves, 26 de noviembre de 2020

Power Query: List.First - Recuperando primera o última coincidencia

Al trabajar sobre tablas es muy frecuente operar sobre los campos numéricos o incluso de tipo fecha para obtener acumulados, promedios o su valor máximo o mínimo... pero en ocasiones nos interesa recuperar información de campos de tipo texto.
Por ejemplo, ¿cuál es la primera o última descripción que corresponde a tal o cual código?
Power Query: List.First - Recuperando primera o última coincidencia

Hoy mostraré cómo recuperar la primera descripción correspondiente a cada país desde nuestra tabla de información (List.First).
Igualmente recuperaremos la última descripción correspondiente a cada país (List.Last).
Y por último un concatenado de las cuatro primeras descripciones asociadas por país (List.FirstN, además de la ya conocida (Text.Combine)).

Comenzaremos cargando nuestra tabla 'Tabla1' al editor de consultas desde la ficha Datos > grupo Obtener y transformar > Desde Tabla o Rango.
Ya en el editor de Power Query y desde el menú Inicio > grupo Transformar > Agrupar por con la columna 'Código' seleccionada.
En la ventana de configuración aplicaremos las opciones avanzadas, ya que vamos a incluir dos operaciones:
1- una para obtener el valor máximo de 'Ventas' por cada páis
2- una operación cualquiera, que a continuación modificaremos, para recuperar la primera aparición de la descripción
Power Query: List.First - Recuperando primera o última coincidencia

Del código M generado para ese paso:
= Table.Group(#"Tipo cambiado", {"Código"}, {{"1st_Descrip", each Table.RowCount(_), Int64.Type}, {"Max_Ventas", each List.Max([Ventas]), type nullable number}})

cambiaremos el que corresponde a la '1st_Descrip' para que quede como sigue:
= Table.Group(#"Tipo cambiado", {"Código"}, {{"1st_Descrip", each List.First([Descripción]), type nullable text},{"Max_Ventas", each List.Max([Ventas]), type nullable number}})


Se observa que hemos llamado a la función M de
List.First(list as list, optional defaultValue as any) as any
que nos devolverá el primer elemento de la lista indicada. En nuestro ejemplo de la lista correspondiente por cada país que genera nuestra agrupación (Table.Group)
Power Query: List.First - Recuperando primera o última coincidencia

De igual forma podríamos obtener la última descripción que hubiera para cada país.
Mismos pasos que los ya comentados, pero esta vez haciendo uso de la función M:
List.Last(list as list, optional defaultValue as any) as any
pero esta vez devuelve el último elemento de la lista, que para nosotros es la lista de descripciones.
La línea de la agrupación debe quedar como sigue:
= Table.Group(#"Tipo cambiado", {"Código"}, {{"Última_Descrip", each List.Last([Descripción]), type nullable text},{"Max_Ventas", each List.Max([Ventas]), type nullable number}})

Power Query: List.First - Recuperando primera o última coincidencia


Finalmente, veremos un último ejemplo que nos permitirá recuperar en una 'celda', concatenadas, las cuatro primeras 'Descripciones' de cada país.
Emplearemos una función ya conocida que nos permite esa concatenación de elementos de una lista:
Text.Combine(texts as list, optional separator as nullable text) as text
Devuelve el resultado de combinar la lista de valores de texto como un solo valor de texto.
Y por otro lado, para obtener la lista de los cuatro primeros elementos emplearemos:
List.FirstN(list as list, countOrCondition as any) as any
sabiendo que:
-si se especifica un número, se devuelve esa cantidad máxima de elementos.
-y que si se especifica una condición, se devuelven todos los elementos que cumplen inicialmente la condición. (OJO!!: Si un elemento no cumple la condición, no se tienen en cuenta más elementos).
Así pues procederemos de igual forma que en los dos casos previos, esto es, agrupando por la columna 'Código' y modificando la línea del código de ese paso hasta que nos quede como sigue:
= Table.Group(#"Tipo cambiado", {"Código"}, {{"Nrd_Descrip", each Text.Combine(List.FirstN([Descripción],4),"-"), type nullable text},{"Max_Ventas", each List.Max([Ventas]), type nullable number}})

Power Query: List.First - Recuperando primera o última coincidencia


List.FirstN devuelve una lista de hasta cuatro elementos, que luego combinamos en una sola cadena de texto con Text.Combine

Sí, existe una función List.LastN...

martes, 24 de noviembre de 2020

Power Query: Alternativas a BUSCARV aproximado

Probablemente un clásico de las relaciones entre tablas sea la búsqueda aproximada, entendiendo por tal la busqueda de un valor por cercania o pertenencia a un intervalo.
Veamos la imagen siguiente:
Power Query: Alternativas a BUSCARV aproximado
Digamos que vamos a replicar el comportamiento de la función BUSCARV en su forma aproximada (cuarto arumento como VERDADERO).
Se trata de identificar el descuento a aplicar de acuerdo al número de unidades vendidas...
De tal forma que si he vendido, por ejemplo, 74 unidades... según mi tabla de intervalo de unidades (Desde 60 hasta 89), le correspondería un descuento del 8%.

Por desgracia en Power Query no existe un tipo de combinación entre tablas que replique tal cual este tipo de relación... por lo que, como en tantas ocasiones, tendremos que ser imaginativos ;-)

Veremos tres maneras altenativas, cada una con sus ventajas e inconvenientes, pero siempre válidas.

Como siempre comenzaremos cargando las dos tablas al editor de Power Query... ya sabes, desde la ficha Datos > grupo Obtener y transformar > Desde Tabla o Rango, solo con conexión.
Importante, sobre la primera Tabla 'TblVENTAS' hemos añadido una Columna Índice desde 1
Power Query: Alternativas a BUSCARV aproximado

Y sobre la segunda tabla 'TblDCTOS' agregamos una Columna personalizada con un texto cualquiera... en mi caso 'salto'
Power Query: Alternativas a BUSCARV aproximado

Igual de importante es cambiar el nombre de la columna 'Desde' por el nombre 'Unidades' (el mismo nombre de la TblVENTAS para ese campo)!!.
Son columnas y cambios que en alguno de los métodos posteriores necesitaremos...

Primer método: Agregar una columna condicional
En el editor de consultas iremos al menú Agregar columna > grupo General > Columna condicional Desde la configuración identificaremos manualmente los diferentes intervalos
Power Query: Alternativas a BUSCARV aproximado

Se observa como hemos ido insertando condiciones simples con el operador 'menor que' para cada intervalo:
Desde Hasta % Descuento
0 14 1%
15 34 3%
35 59 5%
60 89 8%
90 10%
La fórmula añadida a la consulta, desde el asistente, ha sido:
if [Unidades] < 15 then 0.01 else if [Unidades] < 35 then 0.03 else if [Unidades] < 60 then 0.05 else if [Unidades] < 90 then 0.08 else 0.10
que verifica y se ajusta a los descuentos correspondientes...
Un método muy simple de aplicar, sin duda, pero con la limitació obvia que, ante futuros cambios, esto es, nuevos intervalos o variación en los porcentajes... deberemos entrar en la consulta y cambiar manualmente esta configuración.

Segundo método: Trabajar con ambas tablas sin combinar.
Este caso requiere de un uso intermedio del lenguaje M, ya que deberemos picar algo de código... dejando el uso de los asistentes para aspectos residuales... Nuestro código a insertar en una consulta en blanco será:
let
    AddTblVentas=TblVENTAS,
    AddTblDCTO=Table.AddColumn(AddTblVentas, "descuentos", each TblDCTO),
    
    ExpandeDescuentos = Table.ExpandTableColumn(AddTblDCTO, "descuentos", {"Unidades", "Hasta", "% Descuento", "Salto"}, {"Desde", "Hasta", "% Descuento", "Salto"}),

    Reemplazo = Table.ReplaceValue(ExpandeDescuentos,null,Record.Field(Table.Max(Table.SelectColumns(ExpandeDescuentos,"Unidades"),"Unidades"),"Unidades"),Replacer.ReplaceValue,{"Hasta"}),

    AddCondicional = Table.AddColumn(Reemplazo, "Descuentos", each if [Unidades]>=[Desde] and [Unidades]<=[Hasta] then [#"% Descuento"] else 0),
    #"Filtro<>0" = Table.SelectRows(AddCondicional, each [Descuentos] <> 0),
    Columnas_Quitadas = Table.RemoveColumns(#"Filtro<>0",{"Índice", "Desde", "Hasta", "Salto", "Descuentos"})
in
    Columnas_Quitadas

Power Query: Alternativas a BUSCARV aproximado

La esencia de este método es que al juntar en la misma consulta dos tablas (TblVENTAS y TblDCTO) se produce un listado de todas las posibles combinaciones entre los registros de ambas tablas:
Power Query: Alternativas a BUSCARV aproximado

Esto nos permite comparar, con una columna personalizada, con una condición comparativa entre columnas, determinar a qué intervalo pertenecen las unidades buscadas:
if [Unidades]>=[Unidades.1] and [Unidades]<=[Hasta] then [#"% Descuento"] else 0
obteniendo dato solo para el porcentaje buscado, y cero para los demás casos...
El siguiente paso de la consulta es claro, con un filtro sencillo, dejo fuera de la consulta esas filas con valor cero, esto es, aquellas filas que no han cruzado con ningún intervalo!.
Como apunte interesante, y para evitar errores al aplicar en anterior condicional, se reemplazó el valor 'null' del último intervalo de descuento (Desde 90 Hasta 'vacío') con el valor máximo de entre las unidades vendidas:
Reemplazo = Table.ReplaceValue(ExpandeDescuentos,null,Record.Field(Table.Max(Table.SelectColumns(ExpandeDescuentos,"Unidades"),"Unidades"),"Unidades"),Replacer.ReplaceValue,{"Hasta"})
Nos quedamos con el valor máximo (Tabla.Max) de la columna necesaria 'Unidades' (Table.SelecColumns), recuperando el dato con 'Record.Value'.
Lo interesante de este método es que está abierto a cualquier tipo de cambio en la tabla de descuentos... y como inconveniente, el tipo de unión empleada, que podrá generar fallos en la consulta cuando el número de filas en las tablas sea elevado.

Y vamos a por el tercer método: Anexando ambas tablas.
Muy interesante el empleo de esta herramienta 'Anexar consultas' en vez de lo que nos dice el sentido común 'Combinar consultas'.
Comenzaremos desde la consulta de la 'TblVENTAS' para Anexar consultas para crear una nueva, donde seleccionaremos para anexar la TblVENTAS y la TblDCTO con los cambios ya efectuadas al inicio del proceso!!.
Power Query: Alternativas a BUSCARV aproximado

Vemos que el resultado ha anexado, en el campo 'Unidades' en primer lugar los datos de TblVENTAS y a continuación los datos de TblDCTO; los correspondientes al campo 'Desde' que cambiamos de nombre por 'Unidades'.
El sentido del cambio era este... al llamarse ambos campos/columnas iguales, el anexado los une sobre la misma columna.
El resto de campos al no coincidir entre sí los anexa en columnas nuevas...
Power Query: Alternativas a BUSCARV aproximado

En el siguiente paso, muy sencillo, ordenaremos en Ascendente precisamente por la columna 'Unidades'; para seguidamente forzar sobre la columna del '% descuento' la acción de Rellenar Hacía abajo
Power Query: Alternativas a BUSCARV aproximado

Observa como al ordenar por 'Unidades' se han intercalado nuestros intervalos, partiendo de alguna manera nuestras filas entre esos valores... para finalmente rellenar con los porcentajes de descuentos a aplicar para todas esas unidades entre los intervalos necesarios.
Terminamos aplicando un Filtro sobre la columna 'Salto' para eliminar esas filas donde aparezca el texto 'salto' (recuerda que venían de la TblDCTO tratada)... esto es, quitamos las filas de las tablas no necesarias.
Ordenamos por la columna Índice para recuperar el orden original de los registros de ventas.
Y quitamos columnas no necesarias... para llegar a nuestra consulta final
Power Query: Alternativas a BUSCARV aproximado

El código generado:
let
    Origen = Table.Combine({TblVENTAS, TblDCTO}),
    #"Filas ordenadas" = Table.Sort(Origen,{{"Unidades", Order.Ascending}}),
    RellenarHaciaAbajo = Table.FillDown(#"Filas ordenadas",{"% Descuento"}),
    FiltroQuita_salto = Table.SelectRows(RellenarHaciaAbajo, each ([Salto] = null)),
    Ordena_Inicio = Table.Sort(FiltroQuita_salto,{{"Índice", Order.Ascending}}),
    ColumnasQuitadas = Table.RemoveColumns(Ordena_Inicio,{"Hasta", "Salto","Índice"})
in
    ColumnasQuitadas
Un último método sencillo, que no requiere de conocimientos específicos del lenguaje M, y que nos permite la flexibilidad que necesitamos ante modificaciones en nuestras tablas originales...

jueves, 19 de noviembre de 2020

Power Query: Separar por filas múltiples columnas

Recientemente un cliente me solicitaba la generación de una macro (en VBA para Excel) que transformara un listado de registros con ciertos campos concatenados en un listado de elementos únicos...
Tal y como se ve en la imagen siguiente:
Power Query: Separar por filas múltiples columnas

La idea, como se observa en la imagen anterior, es separar en varias filas cada registro, de acuerdo al número de elementos concatenados en diferentes columnas... por ejemplo:
24/05/2020 ES;DE;FR 267 1544;1020;1915
está compuesto de tres registros, con valores correspondientes entre los tres paises y los tres importes de ventas:
ES - 1544
DE - 1020
FR - 1912
El resto de campos debería repetirse en cada columna final mostrada...
Adicionalmente pretendemos hacer un reparto equitativo de las unidades para cada fila generada!!.

Obviamente desarrollé la programación.. pero me quedé pensando si era posible evitar la programación y generar una consulta en Power Query que realizara el mismo proceso... y aquí está ;-)
Veamos como separar múltiples columnas por filas.

Partimos de nuestra fuente de datos (una tabla llamada 'TblDatos'), la cual cargaremos como siempre desde la ficha Datos > grupo Obtener y transformar > botón Desde Tabla o rango.
Una vez en el editor de consultas Agregaremos una columna personalizada nueva con la siguiente fórmula:
= [Unidades vendidas]/List.Count(Text.Split([Paises],";"))
Power Query: Separar por filas múltiples columnas

Con esta fórmula conseguimos el reparto por unidades entre cada fila futura a generar...
Clave para este cálculo la función M:
Text.Split(text as text, separator as text) as list
que generar una lista a partir de una cadena de texto...
Por ejemplo, de la cadena de texto: ES;DE;FR generará una lista {ES DE FR}.

Sobre esta lista (que volveremos a utilizar posteriormente) aplicamos otra función M:
List.Count(list as list) as number
que nos devolverá el número de elementos en dicha lista... 3 en mi ejemplo anterior.
Finalemente forzamos el cociente, esto es, el reparto de 'Unidades vendidas' entre ese conteo de elementos...

Power Query: Separar por filas múltiples columnas
Con el cálculo de unidades repartidas pasamos al siguiente paso...

Con la función M:
Table.FromColumns(lists as list, optional columns as any) as table
crearemos una tabla para cada registro compuesta por el desglose de cada columna.
ES - 1544
DE - 1020
FR - 1912
Power Query: Separar por filas múltiples columnas

Tabla que posteriormente expandiremos...
Para incorporar nuestra fórmula Agregaremos una columna personalizada nueva con la fórmula:
= Table.FromColumns({Text.Split([Paises],";"),Text.Split([Ventas],";")},{"Pais","Ventas"})
Power Query: Separar por filas múltiples columnas

Table.FromColumns genera una tabla de dos columnas (llamadas 'Pais' y 'Ventas') a partir de las listas obtenidas con Text.Split de los campos originales [Paises] y [Ventas], indicado en el primer argumento:
{Text.Split([Paises],";"),Text.Split([Ventas],";")}

Con la 'tabla' creada para cada fila, eliminamos las columnas originales [Paises] y [Ventas] que ya no necesitamos.

Finalmente expandimos la última columna personalizada y extraemos las dos nuevas columnas..
Power Query: Separar por filas múltiples columnas


Nuestros datos ya aparecen como pretendíamos, separados por filas..


Solo quedan un par de pasos simples para acabar con nuestra consulta...
Reordenar la posición de las columnas, y
Cambiar el tipo de dato de la nueva columna de Ventas a type.number
Listos para cerrar y cargar en nuestra hoja de cálculo!!

El código completo, que podemos ver en el editor avanzado sería...
let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],
    Tipo_Cambiado = Table.TransformColumnTypes(Origen,{{"Fecha", type date}, {"Paises", type text}, {"Ventas", type text}, {"Unidades vendidas", Int64.Type}}),
    AddCol_RepartoUDS = Table.AddColumn(Tipo_Cambiado, "RepartoUds", each [Unidades vendidas]/List.Count(Text.Split([Paises],";"))),
    AddCol_CreaTabla = Table.AddColumn(AddCol_RepartoUDS, "NuevasCols", each Table.FromColumns({Text.Split([Paises],";"),Text.Split([Ventas],";")},{"Pais","Ventas"})),
    ColumnasQuitadas1 = Table.RemoveColumns(AddCol_CreaTabla,{"Unidades vendidas", "Ventas", "Paises"}),
    Expande_NuevasCols = Table.ExpandTableColumn(ColumnasQuitadas1, "NuevasCols", {"Pais", "Ventas"}, {"Pais", "Ventas"}),
    Columnas_Reordenadas = Table.ReorderColumns(Expande_NuevasCols,{"Fecha", "Pais",  "RepartoUds", "Ventas"}),
    Tipo_Cambiado1 = Table.TransformColumnTypes(Columnas_Reordenadas,{{"Ventas", type number}})
in
    Tipo_Cambiado1

Power Query: Separar por filas múltiples columnas