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 ) )
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
)
)
Thanks. Not necesarly in power query. The dataset is not too large, so it shouldnt affect the performance. I will try the formula above. Thanks