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

LuisPacheco

New member
Joined
May 30, 2013
Messages
12
Reaction score
0
Points
0
Excel Version(s)
2016
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:

IndexProductUsedInv. LevelInv. Level AmountUsed Current Amount
1A550450
2A1050350
3A250330
4A150320
5A2050120
1B51270
2B51220
3B2012018
4B5012050
1C1210
2C1200
1D1002001000

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

Attachments

  • PQ_Subtract_LinesbyUsed.xlsx
    11.9 KB · Views: 21
Last edited:
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
 
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}})),
    #"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:

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

 

Attachments

  • PQ_Subtract_LinesbyUsed_Rev2_.xlsx
    29.6 KB · Views: 26
Last edited:
Hello Guru's, any idea are very helpful or a different way to resolve my request... :Cry:
 
Back
Top