CONSULTA COMBINADA con búsqueda por rangos en Power Query

NIVEL: INTERMEDIO

Como algunos ya saben, Power Query es una herramienta muy potente para trabajos de ETL.  En muchos aspectos supera ampliamente a las funciones de Excel.  

La combinación de consultas permite hacer una búsqueda en base a uno o varios criterios (en Excel tenemos solo uno) y obtener tablas con los resultados, desde los cuales después decidimos cuales valores extraer.  La combinación por lo tanto reemplaza a los clásicos BUSCARV e INDICE/COINCIDIR con la diferencia que ejecuta una búsqueda en el que es capaz de retornar múltiples coincidencias y puede estar basado en claves compuestas (basadas en más de una columna).

La única debilidad de la combinación de columnas es que no es capaz de manera nativa de reemplazar a la clásica búsqueda del BUSCARV o del COINCIDIR cuando el argumento final se VERDADERO o 1, lo que equivale a la búsqueda por rangos.

He visto en internet algunas ingeniosas técnicas para poder simular este comportamiento pero quiero compartir aquí otro enfoque, buscando simplificar las cosas: creando una función que permita ser utilizada en múltiples ocasiones.

Veamos el caso:

Tenemos dos tablas, que ya han sido cargadas a Power Query.  En una tenemos la tabla de porcentajes que rige según la cuota que tiene que alcanzar un vendedor.   Es decir de 0 a 9999.99 se obtiene 3%, de 10000 a 24999.99 se 2.8% y así sucesivamente.

Lo que queremos simular es el uso de la algo como:

=BUSCARV( [@Monto], PORCENTAJES, 2, VERDADERO )    o

=INDICE( PORCENTAJES[Porcentaje], COINCIDIR([@Monto], PORCENTAJES[Cuota], 1) )

pero que opere como la combinación de consultas en Power Query, generando una tabla (registro) en aquel valor de la coincidencia.   Con la opción en el menú de Combinación de consultas solo se programa la búsqueda exacta (FALSO en el BUSCARV o 0 en el COINCIDIR).

Ahora debemos crear una función que en base a tres argumentos (en la descripción digo tabla pero me refiero al tipo de dato, pues en realidad estamos hablando de consultas de Power Query):

• el valor buscado de la tabla actual:  podría ser un número entero o un decimal
• la tabla en la que se buscará
• un texto que indica la columna de esa tabla en la ques e buscará

Para crear una función se debe crear una consulta en blanco.

colocar un nombre representativo a la función en ir al editor avanzado

Ya en el editor avanzado, borrar lo que esté escrito y copiar el texto de la función.

Ahora expliquemos el funcionamiento de esta función:

La primera línea (valorBuscado as any, tabla as table, columna as text) => diseña la función y establece sus argumentos
La linea let inicia la consulta, la de autor es opcional pero te pediría la mantengas

tablaArreglada1 = Table.RenameColumns renombra la columna de búsqueda a un nombre "fijo", esto debido a que necesitamos tener ese nombre no variable para una instrucción posterior
tablaArreglada2 = Table.SelectRows filtra los registros en los que los valores de esa columna sean <= valorBuscado
max = List.Max calcula el máximo valor en la columna buscada de esos registros
tablaArreglada3 = Table.SelectRows  Elige solo  el registro que cumple con ese máximo
rpta = Table.RenameColumns corrige el nombre cambiado de la columna 

in y la última línea terminan la consulta

Ahora vamos a probar la función.

Creemos una consulta que haga referencia en ventas

Luego, se agrega una columna personalizada

 en la que introducimos una fórmula que invoque a la función:

Y finalmente tendremos generada la tabla resultado de "combinar" las consultas:

Luego se podrá expandir y utilizar la información como una combinación normal.

Dependiendo de la version de Excel (y de Power Query se podría tener disponible el ícono "Invocar función personalizada" que se usaría en lugar de "Columna personalizada"

AQUI EL CÓDIGO DE LA FUNCIÓN PARA COPIAR Y PEGAR EN SUS ARCHIVOS:

(valorBuscado as any, tabla as table, columna as text) => 
    let
      autor = "Jaime Segura P.",
      tablaArreglada1 = Table.RenameColumns(tabla, { {columna, "_ESPECIAL_BUSQUEDA" } } ),
      tablaArreglada2 = Table.SelectRows(tablaArreglada1, each [_ESPECIAL_BUSQUEDA] <= valorBuscado),
      max = List.Max(tablaArreglada2[_ESPECIAL_BUSQUEDA]),
      tablaArreglada3 = Table.SelectRows(tablaArreglada2, each [_ESPECIAL_BUSQUEDA] = max),
      rpta = Table.RenameColumns( tablaArreglada3, { {"_ESPECIAL_BUSQUEDA", columna } } )
    in
      rpta

¡Que tengan un buen fin de semana!