jueves, julio 04, 2019

BUSCARV y Power Query - tercera nota


En las notas anteriores vimos como usar Power Query en lugar de BUSCARV cuando el orden de las columnas nos obliga en la tabla de referencia nos obliga a realizar la búsqueda derecha a izquierda y cuando debemos realizar la búsqueda en base a dos o más criterios.

También mencioné al pasar otra ventaja. Power Query nos permite realizar transformaciones y manejar datos sin alterar los datos originales. Sobre este tema, que es una de las ventajas cardinales de usar Power Query, me extenderé en una futura nota.

En esta nota veremos como resolver la situación en la que la tabla de búsqueda contiene valores repetidos. Por definición BUSCARV dará como resultado el valor de la primera coincidencia e ignorará los restantes (situación natural ya que una celda de Excel sólo puede contener un único valor).

Cuando usamos BUSCARV para extraer un valor de una tabla y ésta contiene valores repetidos, debemos preguntarnos qué valor queremos extraer. El primero, de acuerdo al orden de la tabla, lo obtendremos por definición. Pero si queremos extraer el mayor o el menor o el segundo, etc., ¿cómo haremos?.

Vamos a volver al ejemplo de la nota que publiqué en el mes de julio del 2008 (BUSCARV en listas con valores repetdios). En ese ejemplo cada elemento de la tabla se repetía tres veces.


El desafío era extraer el precio del producto Tornillos que aparece en segundo lugar. Usando BUSCARV obtendríamos 7.65 ya que este es el primero en aparecer en la tabla; el resultado esperado es 9.74. En la nota del 2008 lo resolvimos usando una fórmula matricial de 203 caracteres de largo (!!). Once años más tarde tenemos, por suerte, el Power Query.

Vamos a empezar por asignar una celda para el producto buscado y otra para el orden de aparición del precio. A ambas celdas le asignamos un nombre definido


Podemos ver que el nombre definido "Orden_de_aparición" se refiere a la celda F2 y el nombre "Producto" a la celda F1.

Nuestro segundo paso es crear una consulta a la tabla de productos que guardamos como "solo conexión" (ver la primer nota de la serie)


Ahora haremos algo similar con los nombres definidos, pero con una pequeña "vuelta de tuerca". Seleccionamos la celda F1 y creamos una consulta usando "Obtener datos - Desde tabla o rango". Al abrirse la consulta en el editor de Power Query veremos en el panel de Pasos Aplicados, que PQ ha creado automáticamente dos pasos además de "Origen"



Eliminamos todos los pasos aplicados excepto "Origen" usando la X a la izquierda del nombre o haciendo un clic derecho sobre el nombre del paso y usando Eliminar. Este será el resultado



Ahora apuntamos con el mouse a la celda (en realidad es un registro, no una celdas pero valga la licencia "exceliana"), clic derecho y aplicamos "drill down" (Rastrear desagrupando datos en la lengua de Cervantes, según Microsoft)


con este resultado


Ahora vamos a "Inicio - Cerrar y Cargar en" y seleccionamos la opción "Crear sólo conexión".
Repetimos la operación con la celda F2.

En el panel de consultas veremos, además de la conexión a la Tabla1, las dos conexiones que acabamos de crear


De hecho hemos creado dos parámetros, como lo indica el ícono a la izquierda de la consulta.

En el próximo paso abrimos la consulta Tabla1 en el editor y aplicamos el filtro a la columna Producto con el valor Tornillo

Esto es similar al Autofiltro de Excel. Apretamos "Aceptar"
Prestemos atención al panel de las fórmulas


Podemos ver que PQ aplica la función Table.SelectRows con el valor fijo "Tornillos". Nosotros queremos que la consulta sea dinámica, es decir, que cuando cambiemos el nombre del producto en la celda F1 también la consulta cambie. Para eso vamos a reemplazar el valor "Tornillos" en la fórmula con el nombre de la consulta/parámetro "Producto" que creamos previamente


Prestemos atención que introducimos Producto sin las comillas (de hacerlo con las comillas PQ lo interpretaría como valor fijo, no como parámetro).

El próximo paso es agregar una columna con el número de orden de cada fila. Esto lo hacemos con facilidad usando "Agregar columna-Columna de índice-Desde 1"

Ahora filtramos la columna "Indice" dejando el valor 2, que luego, como ya han adivinado, reemplazaremos en la fórmula por el parámetro "Orden_de_aparición"


Finalmente eliminamos todas las columnas menos "Precio"


y aplicamos "Cerrar y cargar". Como previamente guardamos la consulta como "Solo crear conexión" no podemos en este paso cambiar el destino del resultado. Para hacerlo usamos el menú contextual (clic derecho sobre la consulta)


y en e diálogo que se abre elegimos la celda de destino


¡Voila!


Llego el momento de comprobar que nuestro modelo es realmente dinámico. Cambiamos los valores en F1 y F2, por ejemplo a Clavos y 3, y apretamos "Datos-Actualizar Todo"





miércoles, julio 03, 2019

BUSCARV y Power Query - segunda nota

En el post anterior señalamos algunos de los escenarios en los cuales es preferible usar Power Query en lugar de BUSCARV.

Tratamos el primero de los tres escenarios: cuando el orden de las columnas en la tabla de búsqueda nos obliga a realizar al búsqueda de derecha a izquierda. Si bien podemos sobreponernos a este inconveniente usando una combinación de INDICE con COINCIDIR, la carga de fórmulas en el hoja puede volverla muy lenta. Power Query nos exime de estos problemas.

Otra situación es cuando debemos realizar la búsqueda en base a más de un criterio de búsqueda. Supongamos esta situación


En la tabla de la izquierda (tbl_Calificaciones) tenemos las notas de los alumnos; queremos transportar esta notas a la tabla de la derecha. El problema es que debemos usar dos criterios: nombre y apellido ya que tanto los nombres propios como los apellidos se repiten, pero BUSCARV funciona con un solo criterio de búsqueda.
Podemos superar el problema concatenando el nombre y el apellido en sendas columnas auxiliares en ambas tablas. Pero, como en el ejemplo anterior, ésto nos lleva a alterar nuestras fuentes de datos. También podríamos hacer una concatenación implícita (dentro de la fórmula) pero tendríamos que recurrir a fórmulas complicadas y "pesadas" desde el punto de vista del recálculo de la hoja. Nótese además, que el orden de las columnas es distinto en ambas tablas.

Veamos la solución con Power Query. Empezamos por crear una conexión a la tabla tbl_Calificaciones y las guardamos como "solo conexión"



Luego creamos una conexión a Tabla1 y en editor de Power Query elegimos la opción "Combinar consultas"

En la ventana del editor de Power Query usamos la opción "Combinar consultas" de esta manera



Primero hacemos un clic a la columna Apellido de Tabla1 e inmediatamente después a Apellido de tbl_Calificaciones. Luego manteniendo apretada la tecla Ctrl marcamos las columnas Nombre de ambas tablas (nótese el número 1 al lado derecho de Apellido y el número 2 al lado derecho de Nombre).
Como ya vimos en el caso anterior, Power Query agrega una columna a la Tabla1 con una doble flecha a la derecha. Esta doble flecha nos indica que la columna es de hecho una tabla que podemos expandir y elegir que columnas agregar


En este caso vamos a seleccionar solamente "Calificación"


y este es el resultado


Elegimos "Cerrar y Cargar" y este es el resultado


Este video muestra todo el proceso



En el próximo post veremos el caso de BUSCARV con valores repetidos usando Power Query.

lunes, julio 01, 2019

BUSCARV y Power Query - primera nota

Todo coacher que se precie nos conmina a "pensar fuera de la caja".Cuando de Excel se trata hay pensar fuera y dentro de la caja al mismo tiempo. O mejor dicho, en Excel pensar "fuera de la caja" es también ver qué hay "dentro de la caja", la caja de herramientas de Excel que se va enriqueciendo de versión en versión.

Pensar "fuera de la caja" quiere decir romper nuestros hábitos de pensamiento, recurrir siempre a lo que sabemos y no preguntarnos si habrá alguna otra forma de resolver el problema. Y aquí entra la cuestión de ver que hay dentro de la caja de herramientas de Excel. Power Query y PowerPivot son las herramientas más notables que Microsoft ha incorporado en los últimos años.

BUSCARV (VLOOKUP en inglés) es probablemente una de las funciones más usadas en Excel; en particular por usuarios de nivel básico o intermedio. Sin embargo, BUSCARV tiene varias limitaciones:
  • la búsqueda se realiza de izquierda a derecha, lo que nos en muchos casos a reorganizar el orden de las columnas en nuestra tabla de búsqueda o a utilizar una combinación de INDICE y COINCIDIR;
  • la búsqueda debe realizar en base a un único parámetro o utilizar alguna de las técnicas que muestro en el post "BUSCARV con dos parámetros" (si la dan un vistazo a la complejidad de las fórmulas, traten de imaginar una búsqueda en base a tres o más parámetros);
  • si hay valores repetidos en la columna de búsqueda, BUSCARV siempre dará el primer valor encontrado ignorando los restantes.
Todas estas limitaciones pueden ser superadas con facilidad usando Power Query en lugar de BUSCARV.

Una pequeña introducción al uso de Power Query para aquellos lectores que aún no estén familiarizados con esta herramienta.(quienes esté interesados en ampliar conocimientos recomiendo este curso).



Para usar Power Query, incorporado orgánicamente a Excel desde la versión 2013 o como complemento en Excel 2010, creamos una conexión a la fuente de datos. Estos datos pueden encontrarse en el mismo cuaderno como tabla o en cualquier fuente remota (cuaderno Excel, Access, página WEB, archivo de texto, etc.). Una vez creada la conexión podemos volcar los datos en una hoja de Excel o dejarlos como "solo conexión". Esta última opción nos permite superar la limitación de 1.48 millón filas por hoja.

Vamos ahora a ver el primer caso que menciono más arriba usando tablas extraídas de la base de datos Northwind. Los dos casos restantes los veremos en los próximos posts.

Aclaración: en un caso real sólo crearíamos conexiones a la base de datos sin necesidad de cargar los datos en hojas de Excel. A los efectos del ejemplo, vamos a suponer que los datos están en tablas en hojas de Excel.

Caso 1 - Búsqueda de derecha a izquierda.

Supongamos esta tabla de productos. El identificador de categoría (Category ID) está en la columna C.


En esta tabla de categorías el identficador está en la columna B (la segunda de la tabla) y la descripción en la columna A.


Queremos agregar la descripción de la categoría a la tabla de productos. Para hacerlo con BUSCARV tendríamos que reordenar la tabla de categorías o usar una combinación de INDICE y COINCIDIR.
Con Power Query hacemos lo siguiente:

a. Creamos una conexión a la tabla de productos



En la ventana del editor de Power Query seleccionamos la opción "Guardar como..." y "Sólo conexión"



b. Hacemos lo mismo con la tabla de categorías. En el panel de conexiones veremos ambas conexiones que acabamos de crear




c. Usamos la opción Datos-Obtener Datos-Combinar consultas


En el cuadro que se abre seleccionamos las consultas a combinar (Products y Categories), señalamos las columnas coincidentes de ambas tablas (CategoryID) y seleccionamos el tipo de combinación a "Extrema izquierda" (sin connotaciones políticas... esto quiere decir, traer todos los registros de la primer tablas y sólo los coincidentes de la segunda).
Apretamos "Aceptar" y veremos que se crea una nueva consulta con la tabla Productos a los que se le agregado una nueva columna, "Categories". En realidad esta columna contiene toda la tabla Categories y la doble flecha en el encabezamiento nos permite expandirla y elegir la o las columnas de Categories que queremos traer

    Al expandir la columna, como dijimos, vemos todas la columnas de la tabla. En nuestro caso sólo elegiremos "Category Name"


    Apretamos "Aceptar" y aquí tenemos el resultado


    Todo los que nos queda por hacer es descargar esta nueva consulta a una hoja del cuaderno



    Para quien no esté familiarizado con Power Query este procedimiento puede parecer complicado. ¿Qué más simple que pegar una fórmula con BUSCARV?. Sin embargo, el uso de Power Query tiene varias ventajas:

    • no hay necesidad de hacer cambios en la tabla de búsqueda;
    • en nuestro ejemplo hay sólo 77 filas. En casos con miles o decenas de miles de filas, el uso de BUSCARV hace que el cuaderno responda lentamente con cada cambio que conlleve recalcularlo. Por ejemplo, en una hoja con 100 mil filas (caso real de un colega), cada aplicación de Autofiltro puede llevar varios minutos.
    En el próximo post veremos los dos restantes casos. usar BUSCARV con más de un criterio de búsqueda y BUSCARV con valores repetidos en la tabla de búsqueda.