Merge queries

[gkakun]

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? 

 

[BA_Pete · Accepted solution]

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]

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

[amitchandak]

@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]

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

[BA_Pete]

@gkakun 

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

Hope it works out ok for you.

Pete

[gkakun]

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]

@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

[gkakun]

Looks like your DAX measure is partially working, i have many blank rows, still to understand why some dates not captured 

[v-rzhou-msft]

Hi @gkakun 

If you want to build a calculated column like amitchandak 's reply, you don't need to build a relationship between two tables.

And I think there may be something wrong in your Start Time and End Time.

You see in my red box, Start Time seems to be later than End Time.

This may cause some dates not captured.

Please check your values.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[gkakun]

I have tried @amitchandak  solution, but it must have relationship between the tables to work.