Forum Discussion
Join 2 tables based on multiple conditions
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?
- So, you could create a column in both tables that concatenate the fields to join on?
5 Replies
- So, you could create a column in both tables that concatenate the fields to join on?
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?
- That's probably an issue for ImkeF or @edhauns
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.
