Forum Discussion
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...
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 ) )
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
I have tried amitchandak solution, but it must have relationship between the tables to work.