[Excel][Power Query] Reducing list of shipped goods by returned considering “first out first in”

Are you tired of sifting through rows upon rows of data to account for returned goods in your inventory management? Look no further! In this article, we’ll explore how to reduce a list of shipped goods by returned items in Excel using Power Query, considering the “first out first in” (FIFO) principle. Get ready to streamline your inventory management and impress your colleagues with your newfound skills!

Understanding the Problem

Lets say you’re the inventory manager for an e-commerce company. You have a list of shipped goods, with each item’s product ID, quantity shipped, and date shipped. However, some of these items are returned by customers, and you need to account for these returns in your inventory. The challenge lies in identifying which specific items were returned, considering the FIFO principle.

Product ID	Quantity Shipped	Date Shipped
Product A	10	2022-01-01
Product B	20	2022-01-05
Product A	15	2022-01-10
Product C	30	2022-01-15

In this example, we have four shipments of different products. Now, let’s say we receive returns for some of these items.

Product ID	Quantity Returned	Date Returned
Product A	5	2022-01-20
Product B	10	2022-01-25

The Solution: Power Query to the Rescue!

To tackle this problem, we’ll use Power Query, a powerful data manipulation tool in Excel. We’ll create a query that will reduce the list of shipped goods by the returned items, considering the FIFO principle.

Step 1: Connect to the Data

First, we need to connect to our data. Let’s assume our shipment data is in a table named `Shipments` and our return data is in a table named `Returns`. We’ll create a new Power Query by going to `Data` > `From Other Sources` > `From Microsoft Query`. Then, we’ll select our data sources and click `Load`.

Shipments:
| Product ID | Quantity Shipped | Date Shipped |
| --- | --- | --- |
| Product A | 10 | 2022-01-01 |
| Product B | 20 | 2022-01-05 |
| Product A | 15 | 2022-01-10 |
| Product C | 30 | 2022-01-15 |


Returns:
| Product ID | Quantity Returned | Date Returned |
| --- | --- | --- |
| Product A | 5 | 2022-01-20 |
| Product B | 10 | 2022-01-25 |

Step 2: Merge the Data

Next, we’ll merge the `Shipments` and `Returns` tables based on the `Product ID` column. We’ll do this by using the `Merge Queries` feature in Power Query.

= Table.Merge(Shipments, Returns, {"Product ID"})

Step 3: Add a Column for Returned Quantity

We’ll add a new column to the merged table to calculate the returned quantity for each shipment. We’ll use the `IF` function to check if there’s a matching return record for each shipment.

= Table.AddColumn(Merged, "Returned Quantity", 
    if Merged[Quantity Returned] <> null then Merged[Quantity Returned] else 0)

Step 4: Calculate the Remaining Quantity

Now, we’ll add another column to calculate the remaining quantity for each shipment. We’ll subtract the returned quantity from the original shipment quantity.

= Table.AddColumn(Remaining, "Remaining Quantity", 
    Remaining[Quantity Shipped] - Remaining[Returned Quantity])

Step 5: Reduce the List of Shipped Goods

Finally, we’ll use the `GROUPBY` function to reduce the list of shipped goods by the returned items, considering the FIFO principle. We’ll group the data by `Product ID` and `Date Shipped`, and then calculate the total remaining quantity for each group.

= Table.Group(Remaining, {"Product ID", "Date Shipped"}, 
    {"Reduced Quantity", each List.Sum([Remaining Quantity])})

The Final Result

Voilà! We now have a reduced list of shipped goods, considering the returned items and the FIFO principle.

Product ID	Date Shipped	Reduced Quantity
Product A	2022-01-01	5
Product A	2022-01-10	10
Product B	2022-01-05	10
Product C	2022-01-15	30

This reduced list only includes the shipped goods that haven’t been fully returned, with the correct remaining quantity for each item.

Conclusion

In this article, we’ve demonstrated how to reduce a list of shipped goods by returned items in Excel using Power Query, considering the “first out first in” principle. By following these steps, you’ll be able to streamline your inventory management and make more accurate decisions about your stock levels.

Remember to adapt this solution to your specific needs and data structure. With Power Query, the possibilities are endless!

Frequently Asked Question

Get ready to master the art of reducing lists of shipped goods by returned items, considering the “first out first in” principle, with Excel Power Query!