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.
Bring your supplier field into the transaction table with a merge. Select both the Material number and plant code as the keys to join, then with your newly merged column you can create a new calculated column to handle the transaction date logic.
You can then delete your merged column and just have the calculated one.