Forum Discussion

Caroline_20's avatar
Caroline_20
Frequent Visitor
6 years ago
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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago
    So, you could create a column in both tables that concatenate the fields to join on?
Caroline_20's avatar
Caroline_20
Frequent Visitor
6 years ago

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's avatar
Greg_Deckler
Community Champion
6 years ago
That's probably an issue for ImkeF or @edhauns
  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    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)