Power Query: Associate Each Customer Sale With A Supplier Order ID

jb007

New member
Joined
Jan 23, 2016
Messages
2
Reaction score
0
Points
0
Location
London
Hi,

I have 2 Excel tables: an fSales table containing 1 column, "Sales", and an fOrders table containing 2 columns, "Order ID" and "Order Quantity".
What I need is to associate in Power Query each Customer Sale with an Order Number.
However, there will be situations where a Customer Sale is split between 2 Orders, and I need to determine how many units are coming from which order (this is why I have the last 4 columns in the fOrders table).

A few mentions:
a) The "Running Total Sales" and "Running Total Orders" Columns are not part of the Excel tables, but I thought they will need to be calculated in Power Query, that's why I included them below.
When I started thinking about this exercise, I was under the impression that I need the Running Totals, but maybe there's another approach without them.
b) To solve this exercise, I've tried writing an Array formula in Excel instead of using Power Query, but it is painfully slow, as the fSales table is over 10,000 rows.
c) Adding Calculated Columns in DAX might work, but it'd also be slow, plus I've been told that it's best to solve this in the ETL.
d) For simplicity, there is only 1 SKU in here, but in reality, I have many SKUs in my tables.

Let's take a look at the 2 tables:

Code:
[COLOR=#333333][FONT=Segoe UI]SKU         Sales          Running Total         (1)Units sold        (1)From Order     (2)Units Sold     (2)From Order[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]SKU 1     219           219                           219                        Order 1[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]SKU 1     170           389                           31                          Order 1               139                    Order 2[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]SKU 1     374           763                           361                        Order 2                13                     Order 3[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]SKU 1     137          900                           137                         Order 3[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]SKU 1     472          1,372                         472                        Order 3[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]SKU 1     433          1,805                         378                        Order 3                 55                     Order 4[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]SKU 1     115          1,920                         115                        Order 4[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]SKU 1     356          2,276                         356                        Order 4[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]SKU 1     369          2,645                         224                        Order 4                  45                    Order 5[/FONT][/COLOR]
[COLOR=#333333][FONT=Calibri]SKU 1       155             2,800                               155                            Order 5[/FONT][/COLOR]


Code:
[COLOR=#333333][FONT=Segoe UI]SKU   [/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]Order ID          Order Quantity             Running Total Orders[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]A[/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]        Order 1           250                              250 [/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]A[/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]        Order 2           500                              750  [/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]A[/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]        Order 3           1000                            1750   [/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]A[/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]        Order 4           750                              2500[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]A[/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]        Order 5           500                              3000[/FONT][/COLOR]

After having these 2 tables, we need to determine what orders are still in stock, and with how many units.
We can see that there are 200 units (500-145-155) in stock from Order 5.

PS: The reason why I need this, is because it's part of a larger exercise where I want to calculate the Inventory (Orders - Sales) Cost, and to do that, the first step is to associate each Customer Sale with an Order ID.
After this step, I need to calculate how many units are in stock from each order, because each order has a different cost/unit.
 
*Correcting a mistake:
There are 145 (not 45) units associated with order 5 on the second to last row from the first table.
 
Back
Top