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

[LuisPacheco]

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!!

 

[Rudi]

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.

View attachment PQ_Subtract_LinesbyUsed.xlsx

Cheers

 

[MarcelBeug]

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:

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}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Product"}, {{"AllRows", AddColumns, Value.Type(AddColumns(#"Sorted Rows"))}}),
    #"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"

Function AddColumns:

(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
    #"Added Custom1"

 

[Rudi]

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

 

[LuisPacheco]

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

 

[LuisPacheco]

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.

​

 

[LuisPacheco]

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