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