Append Query 2 tables with similar data

[Gr8apmech]

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 and Receipt Date.

Plan is to create measure for 'days late, or early' so that i can show on time delivery of my suppliers.  The common thread between these two tables is the PO number and line number but they have to match.  

When i try to append, it merges the data as one table - full load, then the other table has headers with a lot of null values where i expected to see them align on the PO number and line number.  How can i get these 2 tables to play nice?

[Gr8apmech]

Hi @NaveenGandhi and @Ashish_Mathur .  Any help on the above is much appreciated!

[Gr8apmech]

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

[Ashish_Mathur]

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.

[NaveenGandhi · Accepted solution]

@Gr8apmech 

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

[Gr8apmech]

@NaveenGandhi 

Thanks, the concatenate worked great.  I have not thought of that.  I will research how to do a lookup between the 2 tables which now have PO & Line # concatenated as ABC123,1.

[Ashish_Mathur]

Hi,

If you want to solve it using a calculated column in DAX, then you can use the LOOKUPVALUE() function.  To receive more help, share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[NaveenGandhi]

Hi @Gr8apmech 

Please share sample data and screenshot to understand the problem better.

Regards,
NG