Hacer una operación de fusión en Power Query es bastante simple, pues solo das click al botón de Combinar Queries, seleccionas tus tablas y/o campos, y listo. ¿Pero qué pasaría si queremos fusionar una tabla A con un subconjunto condicional de una tabla B? Claro que se puede hacer, pero necesitaríamos ajustar nuestro código M en el Editor Avanzado de manera manual.
Bases de datos de SQL Server
En este ejemplo voy a usar la muestra de base de datos WideWorldImportersDW que ofrece Microsoft. También cabe mencionar que debemos tener una copia de SQL Server instalada para poder jugar con Power Query. La parte buena es que la versión de desarrollador de SQL Server es 100% funcional y gratis; la parte mala es que no podemos usarla en producción.
Volviendo al ejemplo, voy a usar dos tablas de la base de datos ya mencionada: Fact.Sales y Dimension.Customer. Aquí yo solo quiero que la tabla «Fact Sales» tenga la información del cliente para «Buying Group Tailspin Toys», la cual se encuentra en la tabla «Dim Customer». Esto lo podemos hacer de tres maneras:
- Hacemos la fusión expandiendo la columna de «Buying Group» de la tabla de Customer y luego la filtramos para que nos muestre solo esas filas.
- También podemos pre-filtrar la tabla de Customer para «Tailspin Toys» en la columna «Buying Group» para luego hacer la fusión. Esto necesitaría ser una unión interna, de otro modo obtendremos solo valores nulos en la tabla de «Sales» cuando la expandamos, y luego tendríamos que filtrar esos, lo cual podríamos hacer, pero son más pasos.
- Podemos saltarnos todo esto y hacerlo con un solo paso.
Para no extendernos y hacer esto aburrido nos vamos a saltar los primeros dos métodos e iremos directo al tercero, que es una unión condicional.
Fusíon normal. Combinar sin condicionales.
Primero crearemos la unión como lo haríamos normalmente. En la tabla de Sales vamos a seleccionar «Combinar Queries» desde la pestaña; luego seleccionaremos la tabla de Customers y ahora el campo «Customer Key» en ambas. También deberemos asegurarnos que esta es una unión interna para que así remueva todos los registros de Sale Table que no son «Tailspin Toys». Nuestra ventana de diálogo de Unión deberá verse así:
Daremos click a OK y se hará la unión, pero ahora tenemos que modificarlo para que sea una unión condicional. Cuando hacemos la unión, Power Query escribió el código M que aparece a continuación, el cual podremos ver en el Editor Avanzado. Nótese cómo he agregado feeds entre líneas para que sea más fácil de leer, así como también he expandido la columna de «Buying Group». El paso sobre «#»Merged Queries»» es el que más nos interesa profundizar.
let
Source = Sql.Databases("localhost"),
WideWorldImportersDW = Source{[Name="WideWorldImportersDW"]}[Data],
Fact_Sale = WideWorldImportersDW{[Schema="Fact",Item="Sale"]}[Data],
#"Merged Queries" = Table.NestedJoin(
Fact_Sale,
{"Customer Key"},
dim_customer,
{"Customer Key"},
"dim_customer",
JoinKind.Inner),
#"Expanded dim_customer" = Table.ExpandTableColumn(
#"Merged Queries",
"dim_customer",
{"Buying Group"},
{"Buying Group"})
in
#"Expanded dim_customer"El campo «Table.NestedJoin» tiene los siguientes parámetros según la documentación oficial:
- table1 como la tabla
- key1 como cualquier cosa
- table2 como cualquier cosa
- key2 como cualquier cosa
- newColumnName como texto
- (opcional) joinKind como un número anulable
- (opcional) keyEqualityComparers como una lista) anulable como tabla
Aquí lo que buscamos es modificar el tercer parámetro, table2, el cual es nuestra tabla Customer. No queremos todo table2, solo queremos table2 filtrada por «Topspin Toys» en el campo «Buying Group».
Fusíon condicionada. Combinar mediante selección.
En el código de arriba necesitaremos reemplazar ese tercer parámetro que es solo una referencia a ”dim_customer”, el nombre de «Customer Table».
Eso lo buscamos reemplazar con el siguiente código:
Table.SelectRows(#"Dimension Customer", each [Buying Group] = "Tailspin Toys")
De modo que el código M completo en el Editor Avanzado se vuelta esto:
let
Source = Sql.Databases("localhost"),
WideWorldImportersDW = Source{[Name="WideWorldImportersDW"]}[Data],
Fact_Sale = WideWorldImportersDW{[Schema="Fact",Item="Sale"]}[Data],
#"Merged Queries" = Table.NestedJoin(
Fact_Sale,
{"Customer Key"},
Table.SelectRows(dim_customer, each [Buying Group] = "Tailspin Toys"),
{"Customer Key"},
"dim_customer",
JoinKind.Inner),
#"Expanded dim_customer" = Table.ExpandTableColumn(
#"Merged Queries",
"dim_customer",
{"Buying Group"},
{"Buying Group"})
in
#"Expanded dim_customer"Y eso sería todo. Si estamos haciendo esto en SQL Server, podemos dar click derecho al paso de Expanded Dimension Customer, que es el paso final donde expandimos la columna «Buying Group» y vemos que View Native Query es mostrado. Esto significa que el query folding está funcionando, lo que nos generará el siguiente código SQL (algunas columnas fueron removidas para hacerlo más corto):
select [$Outer].[Sale Key] as [Sale Key],
[$Outer].[City Key] as [City Key],
[$Outer].[Customer Key2] as [Customer Key],
[$Outer].[Bill To Customer Key] as [Bill To Customer Key],
[$Outer].[Stock Item Key] as [Stock Item Key],
[$Outer].[Invoice Date Key] as [Invoice Date Key],
[$Outer].[Delivery Date Key] as [Delivery Date Key],
[$Outer].[Salesperson Key] as [Salesperson Key],
[$Outer].[WWI Invoice ID] as [WWI Invoice ID],
[$Outer].[Description] as [Description],
[$Outer].[Package] as [Package],
[$Outer].[Quantity] as [Quantity],
[$Outer].[Unit Price] as [Unit Price],
[$Outer].[Tax Rate] as [Tax Rate],
[$Outer].[Total Excluding Tax] as [Total Excluding Tax],
[$Outer].[Tax Amount] as [Tax Amount],
[$Outer].[Profit] as [Profit],
[$Outer].[Total Including Tax] as [Total Including Tax],
[$Outer].[Total Dry Items] as [Total Dry Items],
[$Outer].[Total Chiller Items] as [Total Chiller Items],
[$Outer].[Lineage Key2] as [Lineage Key],
[$Inner].[Buying Group] as [Buying Group]
from
(
select [_].[Sale Key] as [Sale Key],
[_].[City Key] as [City Key],
[_].[Customer Key] as [Customer Key2],
[_].[Bill To Customer Key] as [Bill To Customer Key],
[_].[Stock Item Key] as [Stock Item Key],
[_].[Invoice Date Key] as [Invoice Date Key],
[_].[Delivery Date Key] as [Delivery Date Key],
[_].[Salesperson Key] as [Salesperson Key],
[_].[WWI Invoice ID] as [WWI Invoice ID],
[_].[Description] as [Description],
[_].[Package] as [Package],
[_].[Quantity] as [Quantity],
[_].[Unit Price] as [Unit Price],
[_].[Tax Rate] as [Tax Rate],
[_].[Total Excluding Tax] as [Total Excluding Tax],
[_].[Tax Amount] as [Tax Amount],
[_].[Profit] as [Profit],
[_].[Total Including Tax] as [Total Including Tax],
[_].[Total Dry Items] as [Total Dry Items],
[_].[Total Chiller Items] as [Total Chiller Items],
[_].[Lineage Key] as [Lineage Key2] from [Fact].[Sale] as []
) as [$Outer]
inner join
(
select [_].[Customer Key],
[_].[WWI Customer ID],
[_].[Customer],
[_].[Bill To Customer],
[_].[Category],
[_].[Buying Group],
[_].[Primary Contact],
[_].[Postal Code],
[_].[Valid From],
[_].[Valid To],
[_].[Lineage Key]
from [Dimension].[Customer] as [_]
where [_].[Buying Group] = 'Tailspin Toys'
) as [$Inner] on ([$Outer].[Customer Key2] = [$Inner].[Customer Key])Opciones de rendimiento.
Dado que todo el trabajo está hecho en SQL Server, será muy rápido comparado a cómo correría si el motor de mashup de Power Query lo procesaría.
Si nos encontramos usando otra fuente, como archivos de texto o archivos de Excel, el folding no ocurrirá, pero de igual forma debería funcionar bien dado que solo un filtro ha sido aplicado y no un filtro aplicado por cada fila o cada tabla.
Otra cosa a mencionar es que este método probablemente no sea más rápido que las dos formas que se mencionaron antes, pero el código es más corto, además de que podemos ser más precisos con la función Table.SelectRows() que usamos para filtrar en múltiples campos y/o criterios. De todas formas debemos evitar ser demasiado precisos, pues el folding puede dejar de funcionar y el motor de Power Query no podrá darnos el código SQL. Si el desempeño es una necesidad, entonces los primeros dos métodos son mejores para usar muchos filtros en la condición.
Cualquier duda nos leemos abajo.
