Power Query: valor de búsqueda en otra tabla con combinación

Buscar datos de una tabla u hoja de cálculo es probablemente la actividad más común que realizan los usuarios de Excel para crear informes. Aprender a usar BUSCARV para muchos es su primera prueba del poder de Excel. ¿Pero qué pasa cuando usan Power Query? No hay BUSCARV función, entonces, ¿cómo podemos buscar un valor de otra tabla con Power Query? Bueno... en Power Query, usamos la transformación de combinación.

La transformación de combinación en Power Query nos permite unir tablas utilizando una referencia común. Por lo tanto, puede hacer más que simplemente buscar valores de otra tabla. Al final de esta publicación, estoy seguro de que apreciará el poder adicional que nos brindan las fusiones.

Tabla de contenido

• Descripción general
• Guión
• Cargue los datos en Power Query
• Valor de búsqueda en otra tabla con una coincidencia exacta
• Tipos de uniones
• Uniones exteriores
• Unir internamente
• Anti uniones
• Valor de búsqueda de otra tabla con una coincidencia aproximada
• Múltiples coincidencias
• Búsqueda de criterios múltiples
• Búsqueda de coincidencias aproximadas
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0111 Datos de búsqueda.xlsx

Tener acceso

Descripción general

Efectivamente, existen tres tipos de búsqueda: una coincidencia exacta, una coincidencia aproximada y una coincidencia aproximada.

• Coincidencia exacta: la más común y requiere que el valor de búsqueda sea idéntico.
• Coincidencia aproximada: busca el valor por encima (o por debajo) del valor de búsqueda.
• Coincidencia aproximada: busca valores en función de su similitud con otros valores mediante un algoritmo de coincidencia de patrones.

Power Query puede realizar todos estos tipos de búsquedas, aunque en esta publicación nos centramos principalmente en los dos primeros.

Guión

En el archivo de ejemplo 0111 Lookup Data.xlsx hay tres tablas:

• Ventas: Contiene datos de ventas mensuales.
• Clientes: Contiene información de contacto del cliente.
• Reembolsos: detalla el descuento otorgado a los clientes por comprar sobre valores específicos.

Para ilustrar cómo buscamos un valor en otra tabla en Power Query, crearemos dos informes.

Ventas totales por ciudad (búsqueda con una coincidencia exacta)
Los valores de ventas y la ciudad de cada cliente están en tablas separadas. Por lo tanto, para calcular las ventas totales de cada ciudad, necesitamos usar la transformación Merge para unir las tablas.

Reembolso total por cliente (búsqueda con una coincidencia aproximada)
Para obtener el reembolso de cada cliente , necesitamos realizar una coincidencia aproximada. No necesariamente queremos igualar el valor exacto, sino encontrar la banda en la que cae el valor de las ventas. Todavía usamos la función Fusionar para buscar el valor, pero necesitaremos algunas transformaciones más para obtener el resultado correcto.

Cargue los datos en Power Query

Comience cargando las tres tablas de ejemplo en Power Query.

Haga clic en cualquier celda de la tabla Ventas, luego haga clic en Datos de la tabla/rango en la cinta de Excel.

Se carga el Editor de Power Query.

Las tablas de ejemplo están configuradas específicamente para que requieran transformaciones mínimas, pero rara vez es tan simple en el mundo real. En este ejemplo, se pueden utilizar los tipos de datos predeterminados aplicados por Excel.

Para nuestro ejemplo, no necesitamos cargar los datos en la hoja de trabajo. En su lugar, cargamos los datos como una conexión. Desde la cinta de Power Query, haga clic en Inicio Cerrar Cargar en…

Seleccione Solo crear conexión en la ventana Importar datos y luego haga clic en Aceptar .

Repita esta acción con las tablas Clientes y Reembolsos en el archivo de ejemplo. La única diferencia es aplicar un tipo de datos Porcentaje a la columna Porcentaje de reembolso de la consulta Reembolsos .

En Excel, abra el panel Conexiones de consultas (haga clic en Conexiones de consultas de datos si no está visible) y las tres consultas deberían aparecer en la lista.

Ahora estamos listos para comenzar a usar la función Fusionar.

Valor de búsqueda en otra tabla con una coincidencia exacta

Para ilustrar una coincidencia exacta, crearemos un informe de ventas totales por ciudad.

Volvamos al editor de Power Query haciendo doble clic en la consulta de Ventas dentro del panel Consultas y Conexiones .

En el editor de Power Query, seleccione Consultas de combinación de inicio (menú desplegable) .

Aquí hay dos opciones, Fusionar consultas y Fusionar consultas como nuevas . La diferencia entre ellas es si la transformación crea una nueva consulta o agrega la tabla fusionada como un paso de transformación dentro de una consulta existente.

Para facilitar, usaremos una nueva consulta. Por lo tanto, seleccione la opción Fusionar consultas como nuevas .

Se abre la ventana Fusionar. Están pasando muchas cosas aquí:

1. Seleccione la primera consulta que se utilizará; en nuestro ejemplo, es la consulta de Ventas .
2. Seleccione la segunda consulta que se utilizará; en nuestro ejemplo, es la consulta del Cliente .
3. Seleccione la columna Clientes de ambas tablas, ya que son las columnas con la referencia común.
4. Join Kind proporciona seis tipos diferentes de fusiones. Para nuestro primer escenario, necesitamos la unión externa izquierda .
5. Haga clic en Aceptar .

Power Query combina las consultas mirando desde la primera tabla a la segunda tabla.

Hay 6 tipos de unión disponibles; Mire la sección siguiente para conocer los otros tipos de unión.

Se crea una nueva consulta, probablemente llamada Merge1 . Se muestra la primera consulta seleccionada en la ventana Combinar, con una columna adicional que contiene la tabla de la segunda consulta.

Haga clic en el ícono de expandir tabla en el encabezado de la columna Clientes . Solo necesitamos seleccionar la columna Ciudad , luego haga clic en Aceptar .

La combinación seleccionada incluía todos los artículos de la tabla Ventas y los artículos coincidentes de la tabla Clientes ; cualquier artículo sin una coincidencia se muestra como nulo. Por ejemplo, el cliente Mega Mart existe en la consulta de Ventas pero no en la consulta de Clientes; por lo tanto, se muestra un valor nulo para la ciudad.

Para completar nuestro ejemplo, usamos la nueva columna para crear un informe resumido. Primero, seleccione la columna Ciudad , luego haga clic en Transformar Agrupar por en el menú.

Se abre el cuadro de diálogo Agrupar por ; haga las siguientes selecciones:

• Agrupar por: Ciudad
• Nuevo nombre de columna: Ventas totales
• Operación: Suma
• Columna: Valor

En la captura de pantalla anterior, hemos elegido Sumar la columna Valor en una columna llamada Ventas totales . Después de hacer clic en Aceptar , se crea un informe resumido de ventas por ciudad (consulte la captura de pantalla a continuación).

Ahora podemos hacer clic en Inicio Cerrar Cargar… para cargar la consulta como una tabla en una nueva hoja de trabajo en Excel.

Si queremos que cada valor tenga una ciudad, debemos agregar Mega Mart y Wilson's of Greenvale a la tabla Clientes . Después de realizar esto y actualizar los datos, tenemos un resumen completo de las ventas por localidad.

Tipos de uniones

En el ejemplo anterior, utilizamos la unión externa izquierda. Antes de ver otro ejemplo, tomemos unos minutos para pensar en los otros tipos de uniones.

Las uniones nos permiten comparar listas y luego devolver los valores correspondientes. Afortunadamente, las descripciones proporcionadas para cada unión son un buen resumen de lo que hace.

Uniones exteriores

Las uniones externas devuelven todas las filas de una o ambas listas. Podemos seleccionar Izquierda, Derecha o Completa, dependiendo de qué lista debe devolver todas las filas.

• Exterior izquierdo: devuelve todos los elementos de la primera lista y los elementos coincidentes de la segunda lista.
• Exterior derecho: devuelve todos los elementos de la segunda lista y los elementos coincidentes de la primera lista.
• Exterior completo: devuelve todos los elementos de ambas listas.

Unir internamente

Una combinación interna devuelve sólo elementos que existen en ambas listas. Si la primera o segunda lista tiene elementos que no están en la otra lista, estos se excluyen del resultado.

Anti uniones

Las antiuniones devuelven los elementos que no coinciden con ningún valor de la otra lista.

• Left Anti: devuelve cualquier elemento de la primera lista que no tenga coincidencias en la segunda lista
• Right Anti: devuelve cualquier elemento de la segunda lista que no tenga coincidencias en la primera lista

Todo esto es algo poderoso y una prueba de que Power Query puede lograr más que BUSCARV, ÍNDICE/COINCIDIR o incluso BUSCARX.

Valor de búsqueda de otra tabla con una coincidencia aproximada

Ahora es el momento de considerar calcular una coincidencia aproximada. Antes de comenzar, si no está seguro de lo que esto significa, lea mi publicación sobre Coincidencia aproximada aquí.

En este ejemplo, calculamos el valor de un reembolso adeudado a un cliente en función del valor de las ventas. Clientes con ventas mayores a:

• £500 reciben un reembolso del 2%
• £750 reciben un reembolso del 5%
• £1,000 reciben un reembolso del 10%

Estos umbrales se incluyen en la consulta de Reembolsos.

Como vimos anteriormente, el valor devuelto por la transformación de fusión es nulo a menos que exista una coincidencia exacta . Por lo tanto, una fusión devuelve un valor sólo si un cliente tiene ventas de exactamente 500, 750 o 1000. Por lo tanto, debemos seguir algunos pasos más para que esto funcione correctamente.

Para trabajar junto con este ejemplo, agregue otra versión de la tabla Ventas en Power Query.

Con la nueva consulta de ventas seleccionada, haga clic en Transformar grupo por .

Cuando se abra el cuadro de diálogo Agrupar por , realice las siguientes selecciones:

• Agrupar por: Cliente
• Nuevo nombre de columna: Ventas totales
• Operación: Suma
• Columna: Valor

Haga clic en Aceptar . La ventana de vista previa ahora muestra la siguiente tabla, con las ventas totales por cliente.

En el último ejemplo, creamos la combinación como una nueva consulta. Esta vez, para demostrar otro método, agregaremos Merge como otro paso a una consulta existente. Haga clic en Inicio Combinar consultas .

En el cuadro de diálogo Fusionar

1. Seleccione la consulta para fusionar con la consulta seleccionada ( Reembolsos en nuestro ejemplo).
2. Seleccione Ventas totales en la consulta Ventas y Banda de reembolso en la consulta Reembolsos .
3. Seleccione Exterior completo como tipo de unión.
   Nota: En la parte inferior muestra 0 coincidencias en ambas tablas. No te preocupes; esto está bien para este escenario.
4. Haga clic en Aceptar .

Expanda la columna Reembolsos, incluya ambas columnas y luego haga clic en Aceptar .

La ventana de vista previa ahora se ve así:

A continuación, debemos combinar las columnas Ventas totales y Banda de reembolso en una sola columna. Para esto, escribiremos una declaración if. Podríamos usar la función Columna condicional , pero me resulta más fácil escribirla como una fórmula. Haga clic en Agregar columna Columna personalizada .

En el cuadro de diálogo Columna personalizada , proporcione un nombre de columna e ingrese el siguiente texto en el cuadro de fórmula:

if [Total Sales] = null then [Rebate Band] else [Total Sales]

Power Query utiliza una sintaxis diferente a la de Excel para escribir fórmulas, pero con suerte es comprensible con solo leer el texto. Cubrimos las declaraciones if de Power Query con más detalle en otra publicación: Declaraciones if de Power Query .

Hagamos algunas transformaciones más:

• Ordene la nueva columna seleccionando el encabezado de la columna y luego haga clic en Inicio AZ .
• Seleccione la columna Porcentaje y haga clic en Transformar relleno (menú desplegable) hacia abajo .

La ventana de vista previa debería verse similar a la siguiente:

En la columna Porcentaje de reembolsos, debemos reemplazar nulo con 0. Haga clic en Transformar Reemplazar valores y use lo siguiente:

• Valor a encontrar: nulo
• Valor a reemplazar: 0

Notará que cada cliente ahora tiene un valor total y un porcentaje de reembolso . Esto significa que podemos calcular el valor del reembolso.

Sólo quedan unos pocos pasos para ordenar la consulta:

• Filtrar los valores nulos de la columna Cliente
• Elimine todas las columnas excepto Cliente , Ventas totales y Porcentaje de reembolso.

Finalmente, terminemos calculando el valor del reembolso. Seleccione las columnas Ventas totales y Porcentaje de reembolso , haga clic en Agregar columna Multiplicar estándar .

Cambie el nombre de la nueva columna de Multiplicación a Valor de reembolso . Luego podemos Cerrar Cargar la tabla en Excel.

En este ejemplo, hemos simulado una búsqueda de coincidencias aproximadas mediante la transformación de combinación de Power Query.

Múltiples coincidencias

¿Qué sucede si varios elementos pudieran coincidir entre tablas? En este escenario, BUSCARV, ÍNDICE/COINCIDIR y BUSCARX en Excel devuelven el primer elemento encontrado. La fusión se comporta de manera diferente.

Fusionar devuelve cada instancia de un artículo coincidente. Entonces, por ejemplo, supongamos que tenemos dos tablas, una con información del producto y otra con datos de stock sobre esos productos.

Si usáramos una combinación externa izquierda en la columna Tamaño , los elementos de tamaño M se duplicarían ya que hay dos M en la tabla de búsqueda.

Si solo desea hacer coincidir un elemento, elimine los valores duplicados de una de las tablas antes de realizar la combinación.

Búsqueda de criterios múltiples

La buena noticia es que Power Query no nos restringe a una lista. Digamos que queremos hacer coincidir tres columnas. Eso no es problema.

El orden en el que seleccionamos las columnas determina qué columnas coinciden. Por ejemplo, mire la captura de pantalla a continuación. Las columnas seleccionadas en la primera tabla fueron Color, Tamaño y luego Ubicación (en ese orden). Los números en el encabezado de la columna identifican el Orden en el que se seleccionaron los elementos. Las columnas seleccionadas en la segunda tabla deben estar en el mismo orden.

Con este enfoque, podemos realizar búsquedas de varias columnas entre tablas.

¡Qué fácil es eso!

Búsqueda de coincidencias aproximadas

Al observar la ventana Combinar en nuestros ejemplos, ¿notó la opción Usar coincidencia aproximada para realizar la combinación ? Esta transformación utiliza un algoritmo para hacer coincidir valores similares. Por ejemplo, con una coincidencia aproximada, es posible hacer coincidir "Power Query ” con “power-query”. No puede hacer eso con las funciones de búsqueda de Excel. Incluso podemos cambiar los umbrales de qué tan similares deben ser los valores antes de que coincidan.

Esta es una característica bastante avanzada que requiere una consideración cuidadosa. Por lo tanto, no entraremos en detalles aquí, pero puedes encontrar más información en estos posts:

• Universidad de Excel: coincidencia aproximada con Power Query
• Microsoft: compatibilidad con coincidencias aproximadas para Get Transform (Power Query)

Conclusión

Power Query no tiene funciones de búsqueda, como BUSCARV, ÍNDICE/COINCIDENCIA o BUSCARX. Sin embargo, al utilizar la transformación de fusión, aún podemos crear coincidencias exactas y aproximadas para buscar valores en otra tabla.

Leer más publicaciones en esta serie

1. Introducción a Power Query
2. Obtener datos en Power Query: cinco fuentes de datos comunes
3. DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
4. Utilice el editor de Power Query para actualizar consultas
5. Conozca las opciones de Power Query Cerrar Carga
6. Parámetros de Power Query: 3 métodos
7. Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
8. Anexar Power Query: combine rápidamente muchas consultas en 1
9. Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
10. Listar archivos en una carpeta subcarpetas con Power Query
11. Cómo obtener datos del libro actual con Power Query
12. Cómo desvincular en Excel usando Power Query (3 formas)
13. Power Query: valor de búsqueda en otra tabla con combinación
14. Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
15. Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
16. Declaración If de Power Query: condiciones anidadas ifs múltiples
17. Cómo utilizar Power Query Group By para resumir datos
18. Cómo utilizar las funciones personalizadas de Power Query
19. Power Query: errores comunes, cómo solucionarlos
20. Power Query: consejos y trucos

---