Forum Discussion

Gr8apmech's avatar
Gr8apmech
Icon for Helper I rankHelper I
2 years ago
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 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?

Need Help
  • NaveenGandhi's avatar
    NaveenGandhi
    2 years ago

    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

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    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.

  • Gr8apmech's avatar
    Gr8apmech
    Icon for Helper I rankHelper I
    2 years ago
    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
     
    • NaveenGandhi's avatar
      NaveenGandhi
      Icon for Memorable Member rankMemorable Member
      2 years ago

      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's avatar
        Gr8apmech
        Icon for Helper I rankHelper I
        2 years ago

        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's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 years ago

      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.