Merge two tables in Power BI equivalent to this SQL query (with if condition)

I totally agree with christinepayton 

Since you want to try it out, there are many ways to achieve this kind of conditional join.

This is little hard way, but if needed we can do this way.

One such way is below:

Approach:

Load tables separately i.e., Load first and second table in PQ

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00Dc01DcyMDIGcQyROI6GSrE60UpGUEVGyIrgHEcjsCJjqCJjZEVwjhPEJBOoIhNkRXCOM1BRLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Started = _t, Completed = _t, value1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Started", type date}, {"Completed", type date}, {"value1", type text}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDIGUoZGII4jiDBUitWJVjKCyJmABAyNUOWMIXKmIAETU5icAVjOBMlMUzMg6YzQZ4osZwiTA+qLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, fk_period = _t, budget = _t, value2 = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"fk_period", Int64.Type}, {"budget", Int64.Type}, {"value2", type text}, {"Status", Int64.Type}})
in
    #"Changed Type"

Merge in PQ using column/matching rows concept

let
    Source = MergeTable1,

    #"Added Custom" = Table.AddColumn(Source, "Custom", (x) => 
                               Table.SelectRows(MergeTable2, 
                                        (y) => x[value1] = y[value2] 
                                                and Date.Year(x[Started]) = y[fk_period] 
                                                and Date.Year(x[Completed]) = y[fk_period] 
                                                and y[Status] = 1)),

    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID", "fk_period", "budget", "value2", "Status"}, {"ID.1", "fk_period", "budget", "value2", "Status"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"fk_period", Int64.Type}, {"budget", Int64.Type}, {"value2", type text}, {"Status", Int64.Type}, {"ID.1", Int64.Type}})
in
    #"Changed Type"

Hope it helps!