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