Search code examples
joinpowerbipowerquerypowerbi-desktopm

Power Query Conditional JOIN - JOIN with WHERE clause

How to join two tables with a condition?

TABLE A

from to commission
01/01/2021 12/08/2021 0,2
13/08/2021 31/12/2021 0,3

TABLE B

date client price
07/03/2021 Client A 23 €
08/05/2021 Client B 32 €
14/09/2021 Client C 44 €

EXPECTED OUTPUT

date client price commission
07/03/2021 Client A 23 € 0,2
08/05/2021 Client B 32 € 0,2
14/09/2021 Client C 44 € 0,3

I want to add the COMMISSION that applies to a sell depending on the DATE of the sell. I should join the two tables on "TABLE A"[FROM DATE] < "TABLE B"[DATE] AND "TABLE B"[DATE] < "TABLE A"[TO DATE].

I do not want to join every register in both tables and check that condition after the join. I want to check the condition on the join itself.

Solution

  • enter image description here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDWNzIwMlRQ0lFyzslMzStRcASxjYwVHjWtUYrVASqy0DcwRVfkBGIbG8EVGZroG1iiK3IGsU1MIIpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, client = _t, price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"client", type text}, {"price", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=> Table.SelectRows(TableA, (y)=>  x[date] >= y[from] and x[date] < y[to] )),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"commission"}, {"commission"})
in
    #"Expanded Custom"