Forum Discussion

gkakun's avatar
gkakun
Helper III
5 years ago
Solved

Merge queries

Hi, I have 2 tables. one with order ID and timestamp. the other table is the shift rotation, start date, end date and shift number. I want to add shift number to the relevant order ID in the first t...
Need Help
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    Hello @gkakun ,

    You have published this as "Combine Queries". Definitely want to do this in Power Query? It is feasible by a three-way search, but it is quite advanced M code and is not very effective in very large datasets.

    This can be better adapted to doing so as a DAX measure, something like:

    _transShift =
VAR
tTimestamp = MAX(transTable[Trans Timestamp])
RETURN
CALCULATE(
  MAX(shiftTable([SHIFT]),
  FILTER(
    shiftTable,
    shiftTable[Start Time] <= tTimestamp
    && shiftTable[End Time] >= tTimestamp
  )
)

amitchandak's avatar
amitchandak
Super User
5 years ago

gkakun , Table showing shift has been considered as table 2. Create a new column in table 1

 

maxx(filter(Table2, table2[start time] <= Table1[Trans Timestamp] && table2[end time] >= Table1[Trans Timestamp] ), [Shift])

gkakun's avatar
gkakun
Helper III
5 years ago

Thanks! Can I do that also if the tables dont have relashionshipd between them? I dont have a way to create one 

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    gkakun 

     

    I don't think you need a relationship for this.

    Hope it works out ok for you.

     

    Pete

    • gkakun's avatar
      gkakun
      Helper III
      5 years ago

      Hi, thanks again. I have tried the measure above, but unfortunately it's not working. Im not getting an error, i just cant add it to the table- it's keep loading and nothing happened. 

      • BA_Pete's avatar
        BA_Pete
        Super User
        5 years ago

        gkakun ,

         

        If your tables are very large, this may not be very performant and may take a long time to load.

         

        You can try adding it as a calculated column instead using amitchandak 's answer above (which didn't appear on my screen originally). Otherwise, we could try the Power Query lookup and see how that works out for you.

         

        Pete