Complex comparison of two excel files

[scott_od]

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

Customer	Week	Item	Ordered
Customer A	26	ABC-01	10
Customer B	26	ABC-02	50
Customer C	26	ABC-03	60
Customer A	27	ABC-01	110
Customer C	27	ABC-03	20
Customer D	27	ABC-04	95
Customer C	28	ABC-03	80
Customer D	28	ABC-03	50
Customer E	28	ABC-03	20
Customer F	28	ABC-03	15
Customer D	28	ABC-04	20
Customer G	28	ABC-04	60
Customer C	29	ABC-03	60
Customer D	29	ABC-03	90
Customer E	29	ABC-03	60
Customer F	29	ABC-03	50
Customer D	29	ABC-04	20
Customer G	29	ABC-04	15
Customer E	29	ABC-05	15
Customer F	29	ABC-05	5

Table2

Week	Item	Received	Status
26	ABC-01	10
26	ABC-02	55
26	ABC-03	65
27	ABC-01	105
27	ABC-03	15
27	ABC-04	100
28	ABC-01	20
28	ABC-03	90
28	ABC-04	80
28	ABC-05	11
29	ABC-03	120
29	ABC-04	0
29	ABC-05	0

Illustration of the order relationship

Desired Outcome

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

[scott_od · Accepted solution]

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'

[rubayatyasmin]

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]

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