Forum Discussion
Solved
Power Query Editor: custom column based on another table
Hi, I have two tables with the fields as follow: TransactionTbl: Material, transaction date, quantity, cost, plant, supplier. SupplierTbl: MaterialNbr, Preferred supplier, plant code, contract s...
I found a solution. In the SupplierTbl, I can add rows for dates between start and end dates.
(following instructions I found here: https://natechamberlain.com/2018/08/08/how-to-add-rows-for-dates-between-start-and-end-dates-in-power-bi-date-range-data/)
That way, I can merge by matching MaterialNbr, Plant code, and Date.
Hi,
Write this calculated column formula in the TransactionTbl
=calculate(max(suppliertbl[preferred supplier]),filter(suppliertbl,suppliertbl[materialnbr]=earlier(TransactionTbl[material])&&suppliertbl[plant code]=earlier(TransactionTbl[plant])&&suppliertbl[contract start date]<=earlier(TransactionTbl[transaction date])&&suppliertbl[contract End date]>=earlier(TransactionTbl[transaction date])))
Hope this helps.