Forum Discussion

Kenneth_37's avatar
Kenneth_37
Frequent Visitor
4 years ago
Solved

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, EndDa...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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 0

    Step3# 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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 0

Step3# 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

Kenneth_37's avatar
Kenneth_37
Frequent Visitor
4 years ago

Thank you! I think this might just work. I'm still working through it and will report back.