Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

Power Query - Bring value from another table under two conditions

I have two tables in Power Query:

Sales_table > sales made

Cost_Table > Product costs by date

I can't find a way to bring the cost of the product (Cost_Table) to my sales table (Sales_Table).

The conditions should be:

Sales_Table.ITEM = Cost_Table.ITEM

Sales_Table.DATE >= Cost_Table.DATE

The result should be as follows:

result.PNG

I've tried it with table, selectrow and joins... but I don't quite get the solution.

I appreciate any help.

1 Reply

  • Syndicate_Admin , Try a new column like

     

    new column =
    var _max = maxx( filter(Sales_Table, Sales_Table[ITEM] = Cost_Table[ITEM] && Cost_Table[DATE] <= Sales_Table[DATE] ),Cost_Table[DATE] )
    return
    maxx( filter(Sales_Table, Sales_Table[ITEM] = Cost_Table[ITEM] && Cost_Table[DATE]= _max,Cost_Table[Cost] )