Power Query o por qué volví a publicar en el blog

Comencé a publicar notas en este blog en el año 2006 a sugerencia de un compañero de trabajo. Todo el tiempo te consultan, me decía, ¿no te sería más práctico publicar las respuestas en un sitio o blog?

Considerando la propuesta dediqué unos días a leer los distintos blogs y sitios sobre Excel que habitaban en esa época el espacio WWW. Mi primera conclusión fue que no había nada que yo pudiera agregar a lo ya publicado, en particular en inglés pero también en castellano. Hasta que encontré la siguiente frase: "No importa cuán bueno seas, siempre habrá uno mejor que ti". Despojado del deseo, oculto pero deseo al fin, de ser el mejor comencé a publicar con la meta de compartir mis experiencias como usuario de Excel y ayudar a otros a avanzar en el uso de esta herramienta.

En setiembre del 2017, once años y 745 posts más tarde llegué al punto donde consideré cumplido mi ciclo.
Sin embargo, aquí estoy nuevamente, publicando y dialogando con mis lectores poco menos de dos años después de mi "retiro". ¿Que ha pasado?

En los últimos años Microsoft ha  sumado nuevas herramientas a Excel: Tablas ("Listas" en Excel 2003), Power Query y Power Pivot.
Esta nuevas herramientas han revolucionado la forma en que debemos trabajar con Excel. No menos que la introducción de las tablas dinámicas o del Vba.
Y pongo el acento en "debemos" porque para aprovechar estas herramientas deberemos dejar viejos hábitos y entender cabalmente cual es la mejor práctica de trabajo con el nuevo Excel. Y este es el motivo que me ha llevado a renovar mi actividad en el blog: propagar el uso de esta nuevas herramientas y las buenas prácticas en la transformación y manejo de datos en Excel.

Buen fin de semana a todos.

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"

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.