# Thread: Power Query - Subtract line by line, base on the amount used.

1. ## Power Query - Subtract line by line, base on the amount used.

Hello, I'm looking for some guideline in power query, I'm breaking my head without any good results and I know that these case are simple for some of you... What I'm looking are the below results using power query in excel:

Table 1:

Product Inventory Level
A 50
B 12
C 2
D 200

Table 2:

Index Product Used
1 A 5
2 A 10
3 A 2
4 A 1
5 A 20
1 B 5
2 B 5
3 B 20
4 B 50
1 C 1
2 C 1
1 D 100

Results:

 Index Product Used Inv. Level Inv. Level Amount Used Current Amount 1 A 5 50 45 0 2 A 10 50 35 0 3 A 2 50 33 0 4 A 1 50 32 0 5 A 20 50 12 0 1 B 5 12 7 0 2 B 5 12 2 0 3 B 20 12 0 18 4 B 50 12 0 50 1 C 1 2 1 0 2 C 1 2 0 0 1 D 100 200 100 0

I hope that some one can shed a light on this!!

2. I've been waiting around for a while to see if someone more knowledgeable would answer it, so I could learn myself. Its been a while now, so I forced myself into giving it a try. I managed to get there...but it's probably a really long and laborious way of doing it. Hopefully it will work on your data, trusting you don't have thousands of rows to process.

Anyways, hope you can use some of this solution; else hang around for the other guru's to provide more optimized solutions.

PQ_Subtract_LinesbyUsed.xlsx

Cheers

3. To be honest, I didn't analyze Rudi's solution (apologies for that), but I just created the following solution, a query and a function:

Code:
```let
Source = Table.NestedJoin(Table2,{"Product"},Table1,{"Product"},"Table1",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Inventory Level"}, {"Inventory Level"}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Expanded Table1",{{"Product", Order.Ascending}, {"Index", Order.Ascending}})),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Product"}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"Index", "Product", "Used", "Inventory Level", "Inv. Level Amount", "Used Current Amount"}, {"Index", "Product", "Used", "Inventory Level", "Inv. Level Amount", "Used Current Amount"})
in
#"Expanded AllRows"```

Code:
```(Table as table) as table =>
let
Used = List.Buffer(Table[Used]),
#"Added Custom" = Table.AddColumn(Table, "Inv. Level Amount", each List.Max({0,[Inventory Level] - List.Sum(List.FirstN(Used,[Index]))}), Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Used Current Amount", each List.Min({[Used], -1 * List.Min({0,[Inventory Level] - List.Sum(List.FirstN(Used,[Index]))})}), Int64.Type)
in

4. Hey Marcel... no apologies needed!!
I, however, will be scrutinizing your reply as I'm more than positive it's far better than my attempt. (It'll be a learning point for me too!)

Cheers

5. It's works!!! Thank you Rudi & Marcel. I'm really excited!!!! I appreciate your time and the knowledge that each of you dedicated to my case. Kind Regards

6. ## Revision 2

Hi Marcel, I hope that everything goes well, I'm trying to get a new solution from the code that you provided, I'm adding dates control to the Inventory Tables, these consumes needs to be applied according to the expiration date... I hope that you can take a look on the file and provide me some solution.

Kind Regards.

7. Hello Guru's, any idea are very helpful or a different way to resolve my request...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•