Recreate this SQL JOIN in Power BI

[CatPhish]

In my SQL Server database, I have a table with our product history joined to our order table.  I'm struggling with how to recreate this join in Power BI. Hoping someone can help me.

The order products are joined to the product table, where the order date is on or after the product "start date" and less than the product history "end date." Current product data always has an end date of '3000-01-01'.  Example is below...

SELECT * FROM orders
LEFT OUTER JOIN product_hist ON orders.product_id = product_hist.product_id
AND (orders.order_date >= CASE WHEN product_hist.batch_id = 1 THEN '1900-01-01' ELSE product_hist.start_dt END)
AND orders.order_date < product_hist.end_dt

I've been forcing the Power BI product table to only show the current data, i.e. showing only the data where the end date is '3000-01-01'. This is obviously wrong once users are pulling historical data.

Thanks, in advance, for your help!

[Anonymous]

@CatPhish 

Any chance you can load some sample data of the two tables?  This can be done, would just like to see the actual data and mess around with it.

[CatPhish]

@Anonymous , sure but I'm not exactly sure how to do that.

[Levajar]

Did you try using direct query on Power BI ? 

https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

It isn't an best way of enterprise development approach but for small scale implementation - this shouldn't harm any performance. 

Hope it helps!

[CatPhish]

@Levajar , I could do that for a specific query, but was hoping to join my product table to my order data using the dates to accommodate the historical product metadata, in  addition to the current metadata.

[Levajar]

Got you - then you might have to go advance query editor to use join kind or go with "Merge Queries" under "Edit Queries" functionality & set Join Kind as LEFT OUTER

https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query

https://www.youtube.com/watch?v=LYI20JQSXD0

Hope it helps!