Join 2 tables based on multiple conditions

[Caroline_20]

Hi community,

I am fairly new to Power Query and can't figure out how to translate this sql into a power query. I can't do an sql query, as I transform the table once it is in Power BI and only then Table 1 is in the right form.

I have two tables that I want to join on several conditions order, timestamp and name.

Table1

ID	orderId	start	end	name
1	1452	20.01.20 16:51:00	20.01.20 21:59:00	running
2	1654	20.01.20 17:04:00	20.01.20 18:55:00	running
3	1452	20.01.20 21:59:00	20.01.20 23:55:00	done

Table2

id	orderId	timestamp	number	name
8	1452	20.01.20 16:55:00	233
9	1452	20.01.20 17:13:00	203
10	1654	20.01.20 18:27:00	423
11	1452	20.01.20 21:04:00	203
12	1452	20.01.20 22:30:00	265
13	1452	21.01.20 01:22:00	255

Table3 – joined

id	orderId	timestamp	number	Table2.ID
8	1452	20.01.20 17:01:00	233	1
9	1452	20.01.20 17:13:00	203	1
10	1654	20.01.20 18:27:00	423	2
11	1452	20.01.20 21:04:00	203	1
12	1452	20.01.20 22:30:00	265	3
13	1452	21.01.20 01:22:00	255	3

Within sql queries I would tried something along these lines. 

RIGHT JOIN table2 ON table1.orderId=table2.orderId WHERE table2.timestamp BETWEEN table1.start AND table1.end OR WHERE (table2.timestamp BETWEEN table1.start AND (DATEADD(HOUR, 2, table1.end) AND name=”done”

Can anybody help me figure this problem out within power query?

[Greg_Deckler · Accepted solution]

So, you could create a column in both tables that concatenate the fields to join on?

[Caroline_20]

Hi Greg,

you were right. I managed to join the tables with calculated columns checking for my conditions and removing the rows that are useless.

e.g. column: timestamp - start. That column is filtered only for positive values.

[Caroline_20]

I understand that I can use this on orderId + name but how does this help me with the part of timestamp between start and end?

[Greg_Deckler]

That's probably an issue for @ImkeF or @edhauns

[ImkeF]

Hi @Caroline_20  

you have to select the matching rows from Table1 for each row of Table2, so this is not going to be a fast query. To improve the speed a bit at least, you should buffer Table1 (see attached file)

You add a column with this code:

Table.SelectRows(
                    Table1, 
                    (x) => 
                        x[orderId] = [orderId] 
                        and (
                            (x[start] < [timestamp] and x[end] > [timestamp])
                            or ( ( [timestamp] > x[start] and [timestamp] < ( x[end] + #duration(0,2,0,0)))
                                   and x[name] = "done")
                            ))[ID]{0}

x stands for Table1 and you don't need a prefix for Table2 (the keyword "each" in the autogenerated M-code creates the required syntax sugar for it).

[ID] looks up the ID-column of the filtered table and {0} selects the first element from it (M is zero-based indexed)