Forum Discussion
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 start date, contract end date.
In Power Query Editor, I'd like to add a Preferred Supplier field that is equal to the Preferred supplier field of the SupplierTbl, to the TransactionTbl, if the conditions below are met:
Material = MaterialNbr,
plant = plant code,
transaction date >= contract start date,
transaction date <= contract end date.
If not, then leave the filed blank.
Thank you all for your help!
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.
14 Replies
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.Thank you for your help!
That's progress, but after merging, I'm getting a new column with "Table" instead of actual records. What should I do next?
An important detail: in SupplierTbl, I may have multiple records with teh same MaterialNbr and PlantCode, but different contract start date and contract end date and different supplier. For example:Material Nbr Preferred Supplier Plant code Contract start date Contract end date 123456 ABC Corp XYZ 1/1/2023 4/30/2023 123456 DEF Inc. XYZ 5/1/2023 987654 GHI & Co WER 2/1/2023 Can you provide some sample data from both tables?
Hi,
This will be easier to do with DAX as a calculated column. Would you be OK with this approach?
hi Ashish,
for sure. I'd love to see your approach. Thank you!
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.
