Power Query Conditional JOIN - JOIN with WHERE clause-CodePudding

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.

CodePudding user response：

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"

CodePudding user response：

Commison % =
VAR _date =
    SELECTEDVALUE ( 'Table B'[date ] )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'Table A'[commission], 1 ),
        _date >= 'Table A'[from ]
            && _date <= 'Table A'[to ]
    )