Forum Discussion
Power Query Editor: custom column based on another table
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.
You've been very helpful, thank you! That works well, expect for one thing: I now have some duplicate row here and there. That seems to happen for a Material number that has 2 row or more in the SupplierTbl.
I need to hide these duplicate rows because I will later sum up the Cost and I don't want to double count. I'm working on it but if you have any suggestion in the meantime, I'll take it.
If your supplier table has duplicate records you can go to that table, select the fields that would make up a primary key and right click > deduplicate
I don't have any duplicate in my SupplierTbl. But if you look back at my screenshots, there are two records for Material number 10009090, both with Plant code POBG. Thus, in my TransactionTbl, every time I have a row with Material number 10009090 and Plant code POBG, two rows will be created after merging.
Can you remove the test supplier? Or was this just an example to illustrate the issue?
This is an example to actually illustrate the issue: over time, the preferred supplier is going to change for a given material.
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.