Join 2 tables based on multiple conditions

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)