Fusionar Tablas

Realizando un BUSCARV (,FALSO) en Power Query para Excel

El Caso

Si has trabajado con Excel por cualquier período de tiempo, es casi seguro que te has encontrado con una tabla que tiene su información base, y algunos más tablas que contienen los registros relacionados. Llamamos a esas otras tablas de “búsqueda” o tablas de “referencia”, ya que contienen la información faltante que muchas veces queremos mostrar en nuestra tabla de datos principal, permitiéndonos hacer un mejor uso del conjunto de herramientas de Excel. Este proceso se llama comúnmente el enriquecimiento de una tabla o denormalización de una tabla, y se resuelve por la mayoría de los usuarios de Excel a través de una combinación de fórmulas, incluyendo:

  • BUSCARV
  • BUSCARH
  • una combinación de funciones como INDICE/ COINCIDIR y otras funciones

Esta vez, vamos a crear un proceso dinámico con Power Query que funciona más rápido y es más intuitivo que BUSCARV.  Así que prepárate para decir adiós a la era de BUSCARV y saluda a Power Query para Excel

Descarga el Archivo

Nuestros Objetivos

Tenemos estas tres tablas individuales que queremos fusionar:

  1. Una tabla de Orders (órdenes) que contiene los productos que fueron vendidos así como la fecha y el precio de los mismo junto con otros atributos de la orden
  2. Una tabla de Products (Products) que contiene una lista con valores únicos en cuanto a productos y los atributos de dicho producto, y
  3. Tabla Customer (Clientes) que contiene información sobre cada cliente

Nuestro objetivo es fusionar las 3 tablas utilizando la tabla Orders como nuestra tabla base, agregando registros relacionados y atributos de las otras dos tablas individuales (Customer y Products) por cada fila en Orders. Nuestro resultado final será una tabla con la misma cantidad de filas que tenemos en Orders pero mucho más ancha horizontalmente debido a que tendremos nuevas columnas que traeremos de las tablas Customer y Products.

Paso 1: Subir los datos a Power Query

Nuestros datos ya están muy bien organizados. Todas las tablas ya están definidas como tablas dentro de nuestro archivo de Excel.  Nuestro próximo paso es tomar ventaja de eso y crear conexiones de Power Query a esas tablas. Para hacer esta referencia necesitamos hacer lo siguiente:

  1. Dar clic en cualquier celda de la tabla Orders
  2. Seleccionar el tab de Power Query
  3. Dar clic en el botón con nombre Desde Tabla
  4. Una vez la ventana del Editor de Consulta de Power Query se haya abierto, damos clic en el botón de Cerrar y Cargar, luego damos clic en  Cerrar y Cargar en
  5. Ahora obtenemos una nueva ventana de diálogo que nos dará diferentes opciones sobre dónde podemos cargar dicha consulta. Seleccionaremos Crear solo conexión y luego damos clic a Cargar
  6. Repetimos los pasos 1-5 para las tablas ProductsCustomers

Paso 2:  Fusionemos las tablas

Para esta segunda parte, todo lo que tenemos que hacer es referenciar nuestra tabla base (o consulta) y en este caso utilizaremos nuestra consulta hecha sobre la tabla Orders como base. Vamos a la cinta de Power Query y seleccionamos Mostrar Panel para poder ver todas las consultas en un panel a nuestra mano derecha. Luego le damos un clic derecho sobre la consulta con nombre Orders y seleccionamos Referencia. Esto esencialmente sólo extrae los resultados de la primera consulta en una completamente nueva consulta, que se verá en el panel de consulta como Orders (2). Le damos clic derecho a esta nueva consulta y elegimos Editar para comenzar un proceso de fusión o combinado.

Una vez estemos dentro de la ventana del Editor de Consultas de Power Query, le damos clic al botón con nombre Combinar Consultas. Esto nos brindará una nueva venta de diálogo que nos asistirá en la combinación o fusión de consultas.

Necesitamos definir qué tabla combinar con nuestra tabla actual (Orders) y qué columnas contienen los valores a utilizar para poder hacer una unión entre ambas consultas.

Esta es la parte difícil de la fusión.  Justamente como cuando trabajamos con BUSCARV, estamos tratando de tomar una tabla que tiene una columna con muchos valores en el mismo, y buscar los valores de una columna que tiene una lista única de valores. Es importante que la consulta base comienza como el lado “varios” de la relación, y que la columna de “búsqueda” sea el lado “uno” de la relación.

En este caso nuestra tabla Orders tiene muchos productos en el mismo, pero nuestra tabla Products tiene una lista única de productos. Así que en la parte inferior de la ventana, vamos a elegir para combinar la tabla productos con nuestra tabla base (Orders). Lo siguiente que necesitamos es identificar la columna que es común entre las dos tablas (ProductID en este caso).  

Luego simplemente seleccionamos la columna ProductID en ambas tablas para que aparezcan resaltadas con un fondo verde y luego le damos clic a Aceptar. Dentro de la misma ventana y luego de terminado esto, repetimos el mismo proceso de Combinar Consultas pero ahora con la tabla Customer y la columna CustomerID como se muestra en la imagen anterior. El resultado debe de lucir de la siguiente forma: Como se puede ver en la imagen de arriba, hemos añadido dos columnas que contienen la palabra Table en una fuente verde. Pero es algo más que una palabra … estos son realmente tablas que contienen cada fila de datos de las filas correspondientes de las tablas Customer y Products!

Podemos mirar dentro haciendo clic en el espacio en blanco al lado de la palabra “tabla”, o podemos expandir esas tablas para que podamos obtener las columnas de cada una.

Ahora que sabemos lo que hay dentro de los valores table para cada fila, vamos a expandir dichas columnas. Simplemente hacemos clic en el icono de las flechas de dirección al lado del nombre de la columna para expandir la columna, como se muestra en la siguiente imagen:

De la imagen anterior, se puede ver que eres capaz de elegir las columnas que deseas conservar y cuales no deseas. (Elimina la marca de la casilla que dice Usar el nombre de columna original como prefijo, o recibirás columnas llamadas NuevaColumna.ProductID en lugar de sólo ProductID) 

Expande las otras columnas también – y no te olvides de desmarcar la casilla de Usar el nombre de columna original como prefijo nuevamente si está habilitada.

El último paso es limpiar un poco nuestros datos, definir los Tipos de Datos para las columnas de Fecha y numéricas. Para ellos podemos seleccionar la columna Date, ir al tab de Inicio y luego definir el tipo de datos como Fecha:

Tipo de Datos

Paso 3: Cargarlo al Excel

Y eso es todo – Todo lo que tienes que hacer ahora es ir tab de Inicio, dar clic en Cerrar y Cargar y  elegir donde te gustaría cargar tus datos ya sea en una nueva hoja de Excel o dentro de tu Modelo de Datos (Power Pivot)

Puedes revisar la hoja con el nombre Results para ver un ejemplo de cómo se vería el resultado de esta consulta que hemos creado en este patrón.

Patrones para Power Query
Taller de Power Query

este trabajo ha sido compartido 0 veces
 800
Sobre

 Power Query Training

  (13 Artículos)

Somos un equipo comprometido en ofrecer el mejor contenido para los nuevos y experimentados usuarios de una herramienta en la que creemos: Power Query. Estamos firmemente convencidos de que esta es la mejor herramienta que ha sido agregada a Microsoft Excel en un largo tiempo.