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.
I haven't found a way to upload a file, so here are some screenshots.
1. Go to your transaction table and click merge queries.
2. Select your material number and Plant code for both tables you want to join (hold ctrl for mutli select)
3. You will now have a new column that says "Table" in every row. Click the little arrows that go opposite ways in the column title (see screenshot below).
4. Expand the columns you need for your transaction date logic.
5. Your table should look like this (with a few more fields, I didn't add all your columns)
6. Then go to Add Column > Custom Column> paste this (adjust column names as needed)
if [Transaction Date] >= [Contract Start Date] and [Contract End Date]=null
then [Preferred Supplier]
else if [Transaction Date] >= [Contract Start Date] and[Transaction Date] <= [Contract End Date]
then [Preferred Supplier]
else null
7. Remove the columns you're using to calculate the new column and your table should look like this!