Miguel Escobar Publicada enero 21, 2019

Operaciones de Fusión en Power BI / Power Query – Parte 6: Full Outer Join

Power BIPower BIPower QueryPower Query

¡Este es el último post de la serie! Te recomiendo que leas la Parte 5 de esta serie antes de leer esta, pero si te gustan los retos, puedes intentar leer este artículo si conoces los conceptos básicos de Operaciones de combinación / JOIN dentro de Power BI / Power Query.

Utilizaremos los mismos datos de muestra que usamos en la Parte 5, pero esta vez tendremos un objetivo completamente nuevo que probablemente sea uno de los más frecuentes que he tenido al modelar datos para Power BI.

En esta Parte 6, repasaremos la Unión Externa Completa (Full Outer Join) desde un punto de vista puramente práctico.

Datos Fuente

Usaremos las mismas 2 tablas que usamos para la Parte 5:

  • OnlineSales (Ventas en línea) – vendemos algunos de nuestros productos a través de una tienda en línea alojada en Shopify y esta es la exportación que Shopify nos brinda
image

  • TeleSales (Televentas)– también tenemos un canal diferente al que llamamos TeleSales, que es para todo lo que sucede fuera del canal en línea (OnlineSales).
image

El Objetivo

Esta vez necsitamos una tabla con  TODAS las cuentas (Accounts) que tuvieron ventas en CUALQUIER tabla ya sea OnlineSales y / o  TeleSales.

El resultado que estamos buscando debería tener este aspecto:

image

Nuevamente, esto es diferente a la Parte 5 en el sentido de que no queremos solo los clientes que tuvieron ventas en ambos canales, sino TODAS las cuentas y luego una columna para sus ventas en línea (OnlineSales) y otra para el monto de sus ventas en TeleSales.

La Solución

El siguiente es un proceso paso a paso sobre cómo podemos lograr el resultado deseado con solo unos pocos clics. No se necesita código: solo el uso de Full Outer Join dentro de Power Query / Power BI y algunas otras funciones nativas dentro de la herramienta.

Loading the Data

Puedes seguir las instrucciones descargando el archivo de muestra desde el siguiente botón:

Lo primero que debemos hacer es cargar ambas tablas en Power Query / Power BI. Para hacerlo, simplemente vamos a Data > Get & Transform Data > Get Data > From File > From Workbook:

SNAGHTML8a3bc1

luego encontramos el archivo que descargamos (con el nombre de Sample Data.xlsx). A continuación, seleccionamos la opción «Seleccionar varios elementos» (Select multiple items) y seleccionamos las tablas de OnlineSales y TeleSales:

SNAGHTML2e1f200

y luego hacemos clic en «Transform Data» o «Edit» según la versión de Power Query / Power BI que pueda tener.

Eso debería aterrizar ambas tablas dentro del editor de Power Query como se muestra a continuación:

image

haciendo el Full Outer Join

En esta etapa, estamos listos para hacer clic en el botón «Combinar consultas» (Merge Queries) que se ve en la parte superior central de la imagen anterior. Asegúrate de seleccionar, en el menú desplegable, la opción de combinar consultas como nueva (Merge Queries as New).

image

Ahora obtenemos una nueva ventana para configurar la operación de fusión y aquí depende completamente de usted, lo que desea seleccionar como la primera o la segunda. En mi caso específico, elegí ir con las ventas en línea (OnlineSales) como la primera tabla y TeleSales como la segunda.

Para las columnas que necesitamos seleccionar en cada tabla, simplemente seleccionamos la columna Cuenta (Account) en ambas y luego elegimos el Full Outer desde el menú desplegable en Join Kind como se muestra a continuación:

image

La etiqueta para el tipo de unión dice «todas las filas de ambos» (all rows from both), pero la nota de abajo le da una cantidad de coincidencias de 13. Sé que esto puede resultar confuso, pero realmente no le presto mucha atención a esa nota sobre el número de coincidencias, ya que esta unión específica proporcionará las filas de ambas tablas independientemente.

Después de presionar OK, regresarás al Power Query Editor y deberías de poder verlo así:

image

Expandiendo la columna recién creada

¡Esta es la clave!  puedes expandir la columna recién creada como se muestra en la siguiente imagen:

image

y ahora tu tabla debería verse así:

image

que tiene algunos datos codificados, pero podemos usar la funcionalidad nativa de Power Query para dar forma a los datos como queramos.

Haciendo una columna condicional para definir la cuenta

Notarás que tenemos 2 columnas de cuenta (Account). Uno que teníamos para los datos de OnlineSales y el otro que vino de la operación de expansión de TeleSales Column / Data.

En lugar de tener 2 columnas de cuenta, queremos tener solo 1 y para eso podemos seguir adelante y crear una columna condicional con una lógica simple.

Para crear una columna condicional, iremos a la opción de Agregar columna y seleccionaremos el botón para Columna condicional (Conditional Column).

image

Esto abrirá la ventana de la columna condicional (Conditional Column), donde podemos definir esa lógica condicional para la nueva columna que debería tener este aspecto:

image

En resumen, lo que hace es buscar la columna Cuenta y, si encuentra un valor nulo (básicamente un valor vacío), intentará usar el valor de la columna de TeleSales.Account.

El resultado de esa operación se verá así:

image

Eliminando Columnas

Hay algunas columnas que realmente ya no necesitamos. Vamos a deshacernos de:

  • Account
  • Channel
  • TeleSales.Account

y luego movamos la columna AccountRevised para que sea la primera columna solo por estética. Eso debería verse así:

image

Renombrando Columnas

Solo para asegurarnos de que todo se vea bien y sea fácil de entender, cambiemos los nombres de algunas columnas:

  • AccountRevised debería de ser Account
  • Amount debería de ser OnlineSales.Amount

y por último, pero no menos importante, definamos el tipo de datos de esa columna de Cuenta para que sea un número entero.

El resultado de esto se ve así:

image

Final thoughts

Mira las dos tablas en detalle. Hubo algunas cuentas que solo tuvieron ventas en línea y otras que solo tuvieron ventas en TeleSales, pero tenemos una vista completa de cada cuenta y los números de cada canal de ventas; esto es lo que se llama una vista integrada y no tuvimos que usar una sola línea de código.

Esta es una de las herramientas más poderosas que he podido encontrar dentro de Power Query. Especialmente cuando tienes varios sistemas que no están integrados entre sí, por lo tanto, para fines de informes, debemos vincularlos de alguna manera y Power Query / Power BI puede hacer el trabajo.

Power BIPower BIPower QueryPower Query
Subscribe
Notify of
guest
7 Comentario
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Luis

Hola Miguel
Cuando realizó la operación de combinar 2 tablas ubicadas en libros diferentes para cargar una columna común a ambas, se duplican o triplican los registros
Fv cómo resolver este problema
Gracias!

-Tabla de ventas (incluye una Columna de código de Sku del cliente)
-Tabla de códigos Sku (incluye una Columna de Sku del
Cliente y otra Columba del Sku interno del proveedor)

Daniel montalvo

Tengo 2 tablas Como puedo hacer para que me muestre como resultado los valores encontrados y no encontrados.
Como si estuviera usando el buscarv me es complicado usar el buscarv porque se pone lento ya manejo una data de más de 70 mil registros. Favor du apoyo

Daniel Montalvo

Hola miguel, me podrias enviar un ejemplo si no fuera mucho pedir

Cristian

Hola Miguel, Te cuento mi caso, quiero evaluar una proyección para lo cual tengo:

1 Excel con ventas proyectadas por producto durante mes x
2 Ventas reales extraídas desde otra BD

Quiero comparar si durante ese periodo (mes y año) se cumple mi proyección

Sin embargo cree una tabla Date con fecha min desde la fecha de la info cargada

Pero al realizar segmentación no se filtran los valores para un solo periodo.

Quedo atento