Power Query: join on inequality operator

Artykuł
11/20/2014

In TSQL it’s so easy to create a JOIN with inequality operators like that:

 SELECT * 
FROM Table1
JOIN Table2 ON
    Table1.code = Table2.code AND 
    (Table1.MESERIF_ST >= Table2.Month_Start) AND 
    (Table1.MESERIF_ST <= Table2.Month_End) 
GO

But, right now, it’s a little bit difficult to do that on Power Query.

Simply because with the “merge queries” we can only choose the matching columns to create a merged table.

The related table contains all rows that match each row from a primary table based on a common column value.

To solve the problem we need to use the advanced editor and change the M code.

Let’s try with a demo using two tables on SQL Server.

This is the setup script:

 SET NOCOUNT ON
USE Test
GO

create table Table1
(
id int,
code varchar(10),
MESERIF_ST date
)
GO

create table Table2
(
id int,
code varchar(10),
Month_Start date,
Month_End date
)
GO


insert Table1 values (1, 'aaa','20140101')
insert Table1 values (2, 'bbb','20140111')
insert Table1 values (3, 'ccc','20140121')
insert Table1 values (4, 'ddd','20140131')
GO


insert Table2 values (1, 'aaa','20140105','20140211')
insert Table2 values (2, 'bbb','20140101','20140311')
insert Table2 values (3, 'ccc','20140111','20140411')
insert Table2 values (4, 'ddd','20140202','20140511')
GO

Now, with Power Query I create two Workbook Queries:

I duplicate the first query to start editing and apply a merge on the equality column [code]:

Now I choose my columns:

And this is the result:

In the editor I can see the code of my instructions:

let

Source = Sql.Database(".\sql2012", "test"),

dbo_Table1 = Source{[Schema="dbo",Item="Table1"]}[Data],

Merge = Table.NestedJoin(dbo_Table1,{"abi"},Table2,{"abi"},"NewColumn"),

#"Expand NewColumn" = Table.ExpandTableColumn(Merge, "NewColumn", {"id", "abi", "MESERIF_INIZIO", "MESERIF_FINE"}, {"NewColumn.id", "NewColumn.abi", "NewColumn.MESERIF_INIZIO", "NewColumn.MESERIF_FINE"})

#"Expand NewColumn"

I need to use a SelectRows to define the complete predicate of my query.

So I change the script (you can see in red the statement changed):

let

Source = Sql.Database(".\sql2012", "test"),

dbo_Table1 = Source{[Schema="dbo",Item="Table1"]}[Data],

Merge = Table.NestedJoin(dbo_Table1,{"abi"},Table2,{"abi"},"NewColumn"),

BetweenFilter = Table.SelectRows(#"Expand NewColumn", each ( ([MESERIF_ST] >= [NewColumn.MESERIF_INIZIO]) and ([MESERIF_ST] <= [NewColumn.MESERIF_FINE] ) ) )

BetweenFilter

And this is the result:

The same in TSQL:

Enjoy,

Udostępnij za pośrednictwem

Power Query: join on inequality operator

Dodatkowe zasoby