Merging two tables with joining key being between two timestamps

[kdc02]

Hi, I have two tables that I want to merge / do a full outer join with using ID as the first joining key and if timestamp is between start time (datetime) and end time (datetime).

In SQL, it will be like

a.*, b.*

from a

full outer join b

on a.id = b.id and a.time between b.start_time and b.end_time

Is there a way to do this in Power BI?

Thank you!

[danextian]

hI @kdc02 ,

You can do a full outer join in Power Query. After expanding  the merge column, you can then create a conditional columnthat based on your timestamp logic.  Here's how to do a full outer join: https://learn.microsoft.com/en-us/power-query/merge-queries-full-outer.  

Alternatively, you can use a SQL command when you connect to a database:

Proposed solution is based on information provided. If this doesn't work for you, please post a usable  (not an image, eg:  table1, table2) sample data that covers your use case and your expected result using that sample data.

[kdc02]

Hi @danextian,

My source 1 is data from Sharepoint so I couldn't really use SQL to join them together. I've also tried creating a new column that flags based on the filters I wanted after doing a full outer merge but it just removes the rows and not really joining them together like it would on SQL.

Is there another way to do this?

[danextian]

As mentioned please post a sample data.