Forum Discussion
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 table.
any idea how can I do that?
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 ) )
10 Replies
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])
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
