Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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-powe...)
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.
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!
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?
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!
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?
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-powe...)
That way, I can merge by matching MaterialNbr, Plant code, and Date.
This is an example to actually illustrate the issue: over time, the preferred supplier is going to change for a given material.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
95 | |
83 | |
70 | |
67 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |