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

[CLQ]

Hi all!
In SQL I have this query:

SELECT a.*, b.*
FROM table1 a
LEFT JOIN table2 b
    ON a.value1 = b.value2
    AND (IF b.Status = 1,
              year(a.completed) = b.fk_period,
              year(a.started) = b.fk_period)

Is there a way in Power Bi, whether using DAX or Power Query, to merge two tables, with several conditions, one of them being an if?  In Power Query I know that you can merge tables by selecting multiple columns, but I cannot indicate the "if" condition, and in DAX I am not finding a way to do it.

Thanks!

[sevenhills · Accepted solution]

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!

[christinepayton]

I'm not sure, but just want to point out that you can use SQL statements in SQL connections in the connection step under "advanced", just mentioning in case you haven't seen it. It stops query folding, but I use it in a pinch when I'm trying to get something that is easy to do in SQL but not simple in PQ. 

[CLQ]

Yes, I know the functionality. I'm just curious if there is a way to do that merge in Power Bi 😁