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 ...
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'
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'