Forum Discussion
Conditional joins
Hi, I'm new to using power bi and usually work with SSMS. Hoping to find out if what I want to do is possible in power bi. I think it's best if I use an example.
Table1 columns: StartDate, EndDate, Ward, AvailableBeds
Table2 columns: Patient, Ward, Date, LengthOfStay
I'm hoping to join the two tables above, based on the ward. However, table1 will have multiple records for the wards as the AvailableBeds will change on a periodic basis.
For example Ward A might have:
1 record with a StartDate of 01/01/2020, EndDate 31/03/2020;
1 record with StartDate of 01/04/2020, EndDate 30/06/2020
and a final record with StartDate of 01/07/2020, EndDate 31/12/2021;
In SSMS I would join on ward with the condition that Table2 Date, is between Table1 StartDate and EndDate.
What would be the best approach in PowerBI?
Thanks.
Hi Kenneth_37 ,
Check the following steps in Power Query Editor.
Step1# Inner join merge table1 and table2 with [Ward] and expand tables.
Step2# Create a custom column with below query:
Column = if [Table2.Date] >= [StartDate] and [Table2.Date] <= [EndDate] then 1 else 0Step3# Filter table with column = 1.
Result would be shown as below:
Then you can delete and rename the rows if you need.
Pbix as attached.
Best Regards,
Jay
4 Replies
Hello Kenneth_37
You can join both the tables based on the ward and provide the start and end date slicer.I don't quite think I explained what I'm after well enough.
Because the AvailableBeds changes from month to month and I want to display a yearly snapshot, I would require all of the records from table 1 to still be used.
Thanks for your help anyway.
Hi Kenneth_37 ,
Check the following steps in Power Query Editor.
Step1# Inner join merge table1 and table2 with [Ward] and expand tables.
Step2# Create a custom column with below query:
Column = if [Table2.Date] >= [StartDate] and [Table2.Date] <= [EndDate] then 1 else 0Step3# Filter table with column = 1.
Result would be shown as below:
Then you can delete and rename the rows if you need.
Pbix as attached.
Best Regards,
Jay
Thank you! I think this might just work. I'm still working through it and will report back.
