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
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"
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"
Estimado Jorge, que gusto saber nuevamente de usted; como bien dice Microsoft ha puesto nuevas herramientas que sorprenden y nos inquietan a realizar nuevos procesos de cálculo más dinámicos y menos tediosos. Se ha pasado del lenguaje VBA a lenguaje M que es una herramienta poderosisima. Y todas estas bondandes en manos expertas como las suyas harán un paerte aguas de la forma en que se analizan datos. Saludos y enhorabuena su regreso a este tema.
ResponderBorrarJorge, y si sólo quisiera ver en la tabla resultado (a modo de extracción dinamica) TODOS los valores para un determinado producto ¿como debería poder hacerse?. Que equivaldría a un filtro avanzado sólo que con PQ al ser dinámico y mostrando el resultado en su propia tabla supera al filtro que sólo nos ofrece "copiar en otro lugar" perdiendo la parte dinámica. ¿se puede lograr ese perfeccionismo?.
ResponderBorrarMuchas gracias desde ya.
Alfredo, tendríamos que cambiar el código en el paso del filtrado. Esto tendríamos que hacerlo abriendo la ventana del editor avanzado (la ventana donde se ve el código M) y haciendo una manipulación. El marco de un comentario es demasiado estrecho para mostrar la solución por lo que tal vez lo publique como post (en caso contrario te mandaré la solución por correo privado).
ResponderBorrarBásicamente lo que haríamos es poner una condición de tipo: "si Orden_de_aparición <> null entonces filtrar caso contrario volver al paso anterior" (es decir, no filtrar).
No seria mas practico de esta forma
ResponderBorrarExtraer el segundo en aparecer.
Producto Orden Precio Producto Clavos
tornillos1 tornillos 25560 7.65 Orden de aparicion 2
Clavos1 Clavos 28162 3.95
Tuercas1 Tuercas 29397 5.91 Clavos2 8.06
tornillos2 tornillos 30040 9.74
Clavos2 Clavos 30492 8.06
Tuercas2 Tuercas 31319 6.03
tornillos3 tornillos 33682 7.24
Simplemente cree una columna auxiliar para que me contara el numero de veces en que aparece un prducto
concatene el producto con el orden de aparicion y ya utilice buscarv y listo me llevo 5 segundos.
Saludos!!!
Por supuesto se puede hacer como describes; o mejor aún usando una tabla dinámica. En Excel siempre hay más de una forma de resolver los problemas, pero cuál es la herramienta apropiada depende de varios factores como al estructura de los datos. Supongamos que tu tabla tiene 100 mil o 200 mil filas; en ese caso el uso de fórmulas haría que tu cuaderno recalcule lentamente y sería "pesado".
ResponderBorrar