Forum Discussion

scott_od's avatar
scott_od
Frequent Visitor
3 years ago
Solved

Complex comparison of two excel files

I'm looking for some help please with this scenario which is too complex for me to resolve with my power query knowledge.


Scenario

I have 2 tables:

- tabel 1 contains a list of items ordered by my customers

- table 2 cotains a list of items provided by my supplier in order to fullfill my customer's orders

The supplier is not privvy to my customer's names, so they provide information at item level, which I need to cross reference against my customer's orders to see if they can be fullfilled.

 

Sometimes I receive the correct amount of items, sometime more, sometimes less and sometime I receive items not related to my customer's orders. I would like to add a status column to table 2, to identify whether the items received are (i) not related to my orders, (ii) the correct amount of items, (iii) more items than needed or (iv) less items than needed +which Customer's orders are at risk. The status is checked weekly, so I have provided multiple weeks of data, but stock is not transferable between weeks. 

 

Table1

CustomerWeekItemOrdered
Customer A26ABC-0110
Customer B26ABC-0250
Customer C26ABC-0360
Customer A27ABC-01110
Customer C27ABC-0320
Customer D27ABC-0495
Customer C28ABC-0380
Customer D28ABC-0350
Customer E28ABC-0320
Customer F28ABC-0315
Customer D28ABC-0420
Customer G28ABC-0460
Customer C29ABC-0360
Customer D29ABC-0390
Customer E29ABC-0360
Customer F29ABC-0350
Customer D29ABC-0420
Customer G29ABC-0415
Customer E29ABC-0515
Customer F29ABC-055

 

 

Table2

WeekItemReceivedStatus
26ABC-0110 
26ABC-0255 
26ABC-0365 
27ABC-01105 
27ABC-0315 
27ABC-04100 
28ABC-0120 
28ABC-0390 
28ABC-0480 
28ABC-0511 
29ABC-03120 
29ABC-040 
29ABC-050 

 

 

Illustration of the order relationship

 

Desired Outcome

 

Thanks in advance to anyone spending time & effort to help me with this.

 

 

Need Help
  • scott_od's avatar
    scott_od
    3 years ago

    I've managed to resolve this now, I'm not sure it's the most elegant or efficient solution, but it gives the result I was looking for:

    1. Load both Table 1 and Table 2 into Power Query.
    2. Add 'Key' column to both Table 1 and Table 2 ('Week'-'Item')
    3. New Query 'Table 3' based on Table 1
      • Group by 'Key' (Sum 'Ordered')
      • Merge Table 1 and Table 2 using 'Key'
      • Expand merged table with 'Received'
      • Add a custom column "Status' with the following formula:
        • if [Received] = [Ordered] then "Correct amount"
        • else if [Received] > [Ordered] then "More items"
        • else if [Received] < [Ordered] then "Less items"
        • else "Not related"
    4. Merge Table 1 and Table 3 using 'Key'
    5. Expand merged table with 'Received' and 'Status'

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    Hi, scott_od 

     

    follow the steps 

     

    1. Load both Table 1 and Table 2 into Power Query.
    2. Merge the tables based on the "Item" column.
    3. Expand the merged table to include "Customer" and "Ordered" columns.
    4. Add a custom column with the following formula:
      • `if [Received] = [Ordered] then "Correct amount"
      • `else if [Received] > [Ordered] then "More items"
      • `else if [Received] < [Ordered] then "Less items"
      • else "Not related"
    5. Remove unnecessary columns.
    6. Close & Load the modified Table 2 back into Excel.

    adjust the logic with your exact value

     

    • scott_od's avatar
      scott_od
      Frequent Visitor
      3 years ago

      Hi rubayatyasmin 

      thanks for your suggestion, but the out does not provide the correct result, as it seems to duplicate items & weeks:

       

      I tried adapting your suggestion & doing the merge based on "item" and "week" but in that scenario the results are incorrect for those orders where the total received amount is higher than the individual order, when in fact the total received amount is not enough to cover the item orders for that week

       

       

  • scott_od's avatar
    scott_od
    Frequent Visitor
    3 years ago

    I've managed to resolve this now, I'm not sure it's the most elegant or efficient solution, but it gives the result I was looking for:

    1. Load both Table 1 and Table 2 into Power Query.
    2. Add 'Key' column to both Table 1 and Table 2 ('Week'-'Item')
    3. New Query 'Table 3' based on Table 1
      • Group by 'Key' (Sum 'Ordered')
      • Merge Table 1 and Table 2 using 'Key'
      • Expand merged table with 'Received'
      • Add a custom column "Status' with the following formula:
        • if [Received] = [Ordered] then "Correct amount"
        • else if [Received] > [Ordered] then "More items"
        • else if [Received] < [Ordered] then "Less items"
        • else "Not related"
    4. Merge Table 1 and Table 3 using 'Key'
    5. Expand merged table with 'Received' and 'Status'