BUSCARV y Power Query - nota adicional

En su comentario a la tercera nota de la serie me preguntaba el lector Alfredo cómo haríamos si queremos mostrar todos los resultado. Es decir, filtrar sólo por el nombre del producto.
Obviamente tenemos que crear una condición de manera que si la celda F2 (Orden_de_aparición) esta en blanco no se aplique el filtro (y caso contrario si aplicarlo).

En todas las notas de la serie hemos hecho las transformaciones usando la interfaz de usuario, es decir, sin tener que escribir código por nuestros propios medios.
Power Query nos permite solucionar, por lo menos, el 80% de nuestros problemas de transformación de datos directamente a través de la interfaz de usuario. Esto significa que no necesitamos conocer el lenguaje M y escribir código por nuestra cuenta para solucionar problemas de manejo de datos.

En el caso que nos consulta Alfredo si tendremos que agregar una línea de código. Si bien esto supone ya conocimientos más avanzados, también el usuario principiante o intermedio se beneficiará descubriendo las increíbles posibilidades que nos ofrece Power Query.

En el modelo de la nota anterior introducíamos el nombre del producto en la celda F1 (que habíamos ligado al nombre definido "Producto") y el orden de aparición buscado en la celda F2 (asimismo ligada al nombre definido "Orden_de_aparición"). Al activar "Datos-Actualizar todo", obteníamos el resultado en la celda E6. Si dejamos la celda F2 en blanco, la celda E6 también quedará en blanco. Esto quiere decir que cuando Power Query no encuentra el orden de aparición buscado, el resultado es "null".

Cuando aplicamos pasos en el editor Power Query va generando el código correspondiente a esos pasos

Para crear la posibilidad de mostrar todas las filas del producto en el caso de dejar en blanco la celda F2 (Orden_de_aparición) tendremos que intervenir en el anteúltimo paso ("Filas filtradas1"). Veamos el código creado por Power Query para hacer el filtrado; podemos verlo en la barra de las fórmulas

o en el editor avanzado

En el editor avanzado reemplazamos el paso #"Filas filtradas1" por el siguiente código:

 #"Filas filtradas1" = if Orden_de_aparición<>null then Table.SelectRows(#"Índice agregado",
            each ([Índice] = Orden_de_aparición))
                else #"Índice agregado",

(para facilitar la lectura dividimos el código en varias líneas apretando Enter; de la misma manera podríamos escribir el código en una sola línea).

Como pueden ver estamos creando una condición: Si (if) el valor del parámetro Orden_de_aparición no es "null" (vacío) entonces aplicamos la función Table.SelectRows; en caso contrario (else) nos referimos al paso anterior (#"Índice agregado") sin tomar ninguna acción, que es la forma de ignorar el filtrado.

En nuestro ejemplo si dejamos la celda F2 vacía

tendremos este resutlado

y al cargar la consulta en la hoja y apretar "Datos-Actualizar todo"  veremos

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"