BUSCARV devuelve varias filas y columnas coincidentes

En esta publicación, analizaremos una forma de simular el uso de BUSCARV para devolver múltiples filas y/o columnas coincidentes. ¿Qué quieres decir con “simular” a Jeff? Bueno, BUSCARV está diseñado para devolver un valor único, no varios valores. Es decir, BUSCARV escanea el rango de búsqueda y se detiene en la primera fila coincidente… ignorando cualquier fila coincidente adicional. Una vez que BUSCARV encuentra una fila coincidente, se dispara hacia la derecha para recuperar el valor relacionado de una sola columna. Pero digamos que queremos devolver los valores de varias columnas y todas las filas coincidentes. Bueno, aquí es donde BUSCARV falla. Entonces, simularemos esta funcionalidad recurriendo a Power Query. Vayamos a ello… Tengo un vídeo y una narrativa escrita completa a continuación.

Video

Narrativo

Antes de llegar demasiado lejos, aclaremos nuestro objetivo. Digamos que tenemos una lista de facturas y nuestra lista contiene información resumida como InvoiceID y Customer… algo como esto (tabla de resumen):

También tenemos una lista de detalles de facturas, que contiene detalles de partidas individuales para cada factura. Contiene el ID de factura, el artículo, el importe y quizás otra información. Algo como esto (tabla de detalles):

Ahora queremos revisar cada elemento de la tabla de resumen (cada factura) y recuperar los valores relacionados de la tabla de detalles. Notarás que hay varias filas coincidentes en la tabla de detalles de cada factura. Además, queremos recuperar varias columnas de la tabla de detalles, por ejemplo, las columnas Artículo y Monto.

Cuando hayamos terminado, queremos combinar varias filas y columnas coincidentes como esta:

Dado que BUSCARV se limita a recuperar un solo valor de celda de la primera fila coincidente y la columna de retorno especificada, usaremos Power Query en su lugar.

Lo haremos en tres pasos:

• fusionar consulta
• Especificar varias columnas
• Combina varias filas

Vamos a por ello.

Nota: si se trata de un proyecto único, es posible que pueda usar BUSCARV para recuperar el CustID de la tabla de resumen en la tabla de detalles si usa una coincidencia aproximada y ordena el resumen en orden ascendente por la columna de búsqueda. Luego escriba una fórmula para cada valor de columna que desee devolver. Pero en la práctica, Power Query creará una solución que se actualizará fácilmente en períodos futuros a medida que cambien las tablas de datos.

fusionar consulta

En este primer paso, cargaremos cada tabla en Power Query y luego las fusionaremos. Entonces, primero, seleccionamos cualquier celda en la tabla Resumen y seleccionamos el comando Datos De tabla . La tabla se carga en Power Query:

Luego, hacemos clic en Inicio Cerrar y cargar en… y seleccionamos Solo crear conexión como se muestra a continuación:

Volvemos a realizar los mismos pasos para cargar la tabla de detalles en Power Query.

Ahora necesitamos combinar estas dos tablas con Power Query. Una forma de hacerlo desde el editor de Power Query es seleccionar el comando Inicio Combinar consultas . Una forma de hacer esto desde Excel es seleccionar el comando Datos Obtener datos Combinar consultas Combinar . De cualquier manera, se le presentará el cuadro de diálogo Combinar donde identificará primero la tabla Resumen y luego la tabla Detalles. Luego, identifica la columna de búsqueda seleccionando la columna InvoiceID en ambas tablas, como se muestra a continuación.

Nota: el tipo de unión se deja afuera porque seleccionamos primero la tabla de resumen y luego la tabla de detalles. Si seleccionó Detalle primero y luego Resumen, querrá cambiar el Tipo de unión a Exterior derecho. Dependiendo de en qué esté trabajando, otras opciones de Join Kind pueden resultar útiles, así que échales un vistazo.

Ahora damos clic en Aceptar y nos encontramos dentro del editor de Power Query:

Con nuestras consultas fusionadas, es hora de realizar el siguiente paso.

Especificar varias columnas

Recuerde que una cosa que queremos lograr es devolver múltiples valores de columna. BUSCARV puede devolver un valor de una sola columna, pero podemos devolver fácilmente valores de varias columnas con Power Query.

Para hacerlo, simplemente haga clic en el icono Expandir en el lado derecho del encabezado de la columna Detalle, o en el comando Transformar Columna estructurada Expandir . Podrás elegir una o varias columnas para regresar de la tabla de detalles:

Aquí queremos devolver las columnas Artículo y Monto. Hacemos clic en Aceptar y bam… hemos devuelto varias columnas:

Ahora que hemos devuelto varias columnas, abordemos las filas.

Especificar varias filas

Al final del día, queremos una fila para cada ID de factura, por lo que seleccionamos la columna ID de factura y luego el comando Transformar Agrupar por . Como queremos mostrar una variedad de columnas para cada ID de factura, seleccionamos Avanzado en el cuadro de diálogo Agrupar por resultante :

Ahora, definimos cada columna adicional deseada. En nuestro caso, nos gustaría 3 columnas:

1. Nueva columna denominada Importe, que calcula la suma de la columna Importe existente.
2. Nueva columna llamada Recuento, que contará filas.
3. Nueva columna denominada Elementos, que incluye Todas las filas

El cuadro de diálogo actualizado está aquí:

Damos clic en Aceptar y:

¡Y ya casi llegamos! El último paso es convertir la columna Artículos en una lista separada por comas de los valores reales de los artículos que se encuentran en la tabla de detalles.

Para hacer esto, usaremos un truco que Ken y Miguel me enseñaron en su taller (por cierto, mira su increíble libro M is for Data Monkey ).

Damos clic en Agregar columna Columna personalizada . En el cuadro de diálogo Columna personalizada resultante, ingresamos el nombre de la columna deseada, como ItemList, y escribimos la fórmula que se muestra a continuación.

Nota: el argumento [Elementos] hace referencia al nombre de la tabla y el “Elemento” hace referencia al nombre de la columna.

Al hacer clic en Aceptar, se genera una nueva columna ItemList, como esta:

Ahora, debemos decirle a Power Query que queremos convertir cada lista de la columna ItemList en una lista de valores separados por comas. Para hacer esto, seleccionamos el botón Expandir en el lado derecho del encabezado ItemList y seleccionamos Extraer valores… . En el cuadro de diálogo Extraer valores de la lista resultante , elegimos nuestro delimitador preferido. Aquí, iremos con una coma:

Haga clic en Aceptar y ¡bam!

Podemos eliminar la columna Elementos y luego hacer clic en Inicio Cerrar y cargar en… y enviarla a una Tabla. Los resultados aparecen en Excel así:

¡Y eso es exactamente lo que nos propusimos hacer!

Ahora bien, fueron necesarios bastantes pasos para llegar hasta aquí, pero ahora las mejores noticias. Una vez configurado esto, todo lo que debemos hacer en períodos futuros es hacer clic derecho en la tabla de resultados y seleccionar Actualizar. Las nuevas filas en las tablas de resumen o detalles se incluyen automáticamente. Esto significa que la inversión de tiempo para configurar esto generará dividendos de eficiencia en cada período posterior.

Si tiene otros consejos de Power Query o formas de mejorar este proceso, compártalos publicando un comentario a continuación… ¡gracias!