Results 1 to 5 of 5

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!!
    Attached Files Attached Files
    Last edited by LuisPacheco; 2017-12-04 at 08:55 PM.

  2. #2
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    103
    Articles
    0
    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
    Regards,
    Rudi
    www.eileenslounge.com

  3. #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}})),
        #"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"

  4. #4
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    103
    Articles
    0
    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
    Regards,
    Rudi
    www.eileenslounge.com

  5. #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

Posting Permissions

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