Add value from one table Power BI table to another based on two column conditions

[DuaneDLB]

I have two tables '1' and '2' (see diagram below).

Table1 contains a list of detail orders with a Source and Price.  I would like to add a new column to Table1 called 'Value'.  The value will be populated using the value from Table2. 

I need to use the Source and Price from Table1 and look up the value in Table2.  I have no problems doing a Lookupvalue when the column is a one to one match, but in this case I have to match on the Source and the Beg Price and End Price in Table2.

Both tables have 'Source' to match on, but trying to select the record where the Price from Table1 is assoicated to the Beg Price and End Price of Table2.  Once I find the match in Table2 I would like to store the value into the new column in Table1.   Any suggestions?  Thanks 

[v-kkf-msft · Accepted solution]

Hi @DuaneDLB ,

Please create the new column.

Value = 
CALCULATE (
    SUM ( Table2[Value] ),
    FILTER (
        Table2,
        Table2[Source] = EARLIER ( Table1[Source] )
            && Table2[Beg Price] <= EARLIER ( Table1[Price] )
            && Table2[End Price] >= EARLIER ( Table1[Price] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @DuaneDLB ,

You can do a merge in Power Query to a table from another table. Under Join Kind, select Left outer. This will return all rows in Table with matching rows in Table1. A new column containing tables will be created after the merge. You may expand this and select only the needed/desired columns.