Forum Discussion
Solved
Append Query 2 tables with similar data
I'm attempting to append/merge two tables to one. Reason is one table has Purchase Order Number, Line Number and Due Date but does not have Receipt Rate. Other table has Purchase Order, Line Number...
Please concat po number and line item, then do a lookup to bring the receipt date to purchase table, or you can merge the tables in PQ as well.
Let me know if this works.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Follow me on LinkedIn
Looking for help to make these 2 tables result in the expected result below. Do i merge the tables together or do i use DAX to pull the data from each table to a new table to create the "Days Early or Late" measure? What's the best way to make this work?
Thanks!
Purchase Orders table:
PO Number | Line Item Number | Due Date |
ABC123 | 1 | 7/25/2024 |
ABC123 | 2 | 7/30/2024 |
ABC123 | 3 | 8/15/2024 |
Receipts Table:
PO Number | Line Item Number | Part Number | Receipt Date | ||
ABC123 | 1 | JAC1 | 7/24/2024 | ||
ABC123 | 2 | JAC23 | 7/31/2023 | ||
ABC123 | 3 | JAC42 | |||
Expected Result:
PO Number | Line Item Number | Part Number | Due Date | Receipt Date | Days Early or Late |
ABC123 | 1 | JAC1 | 7/25/2024 | 7/24/2024 | -1 |
ABC123 | 2 | JAC23 | 7/30/2023 | 7/31/2023 | 1 |
ABC123 | 3 | JAC42 | 8/15/2023 | 337 |
Hi,
Write these calculated column formulas in the Receipts table
Due date = LOOKUPVALUE('Purchase orders'[Due Date],'Purchase orders'[PO Number],Receipts[PO Number],'Purchase orders'[Line Item Number],Receipts[Line Item Number])Column = 1*(Receipts[Receipt Date]-Receipts[Due date])Hope this helps.