none
Running Balance with ID RRS feed

  • Question

  • Hello everbody in the world!

    In need some help, resp. an idea if this is possible, resp. whether it makes sense to do so in PQ.

    You can download my sample file here:
    https://www.dropbox.com/s/av9s9ethz4p61br/Running%20Balance.xlsm?dl=1

    Simplified data, the real data has a few thousend lines (I guess always below 10.000 lines).

    I have a Stock table with ID and Amount also an Order table with ID and Amount.
    I like to show in each row how much I can "feed" an Order from the Stock, I want to calculate the values in column Balance.

    That is very simple and fast with a macro, "Tabelle1.RunningBalance" is also included in the file, you can execute it to play around.

    So my questions are:

    Is that possible within Power Query?
    Is the performance good enough (compared to the macro)?

    Andreas.


    Thursday, January 23, 2020 9:45 AM

Answers

  • More flexible solution:

    let
        Source = Excel.CurrentWorkbook(){[Name="Order"]}[Content],
        i = Table.AddIndexColumn(Source, "i"),
        group = Table.Group(i, {"ID"}, {"temp", each
                let
                    rec = List.Buffer(Table.ToRecords(_)),
                    stock = try Table.Join([[ID]], "ID", Stock, "ID")[Amount]{0} otherwise 0
                in
                    List.Transform(List.Zip({rec, List.Generate(()=>[i=0, amt = rec{i}[Amount], st = List.Max({stock-amt,0}), bal = List.Min({stock,amt})],
                                                        each [i] < List.Count(rec),
                                                        each [i = [i]+1, amt = rec{i}[Amount], st = List.Max({[st]-amt,0}), bal = List.Min({[st],amt})],
                                                        each [[bal]])}), Record.Combine)}),
        toTable = Table.FromRecords(List.Combine(group[temp])),
        final = Table.RemoveColumns(Table.Sort(toTable,{"i", 0}),{"i"})
    in
        final


    Friday, January 24, 2020 9:40 AM
  • Hi Andreas,

    This code should work:

    (Stock as table, StockID as text, StockAmount as text, Source as table, SourceID as text, SourceAmount as text) =>
    let
        i = Table.AddIndexColumn(Source, "i"),
        group = Table.Group(i, SourceID, {"temp", each
                let
                    rec = List.Buffer(Table.ToRecords(_)),
                    stock = try Table.Column(Table.Join(Table.SelectColumns(_, SourceID), SourceID, Stock, StockID), StockAmount){0} otherwise 0
                in
                    List.Transform(List.Zip({rec, List.Generate(()=>[i=0, amt = Record.Field(rec{i}, SourceAmount), st = List.Max({stock-amt,0}), bal = List.Min({stock,amt})],
                                                        each [i] < List.Count(rec),
                                                        each [i = [i]+1, amt = Record.Field(rec{i}, SourceAmount), st = List.Max({[st]-amt,0}), bal = List.Min({[st],amt})],
                                                        each [[bal]])}), Record.Combine)}),
        toTable = Table.FromRecords(List.Combine(group[temp])),
        final = Table.RemoveColumns(Table.Sort(toTable,{"i", 0}),{"i"})
    in
        final
    Assumed, there is single key column. Otherwise, the code should be slightly different.
    Monday, January 27, 2020 10:26 AM

All replies

  • Hi Andreas,

    Not sure, this code as fast as macro, but it should work:

    let
        Source = Excel.CurrentWorkbook(){[Name="Order"]}[Content],
        group = Table.Group(Source, {"ID"}, {{"temp", each
                let
                    rec = List.Buffer([Amount]),
                    stock = try Table.Join([[ID]], "ID", Stock, "ID")[Amount]{0} otherwise 0
                in
                    Table.FromRecords(List.Generate(()=>[i=0, amt = rec{i}, st = List.Max({stock-amt,0}), bal = List.Min({stock,amt})],
                                                        each [i] < List.Count(rec),
                                                        each [i = [i]+1, amt = rec{i}, st = List.Max({[st]-amt,0}), bal = List.Min({[st],amt})],
                                                        each [[amt],[bal]]))}}),
        expand = Table.ExpandTableColumn(group, "temp", {"amt", "bal"}, {"Amount", "Balance"})
    in
        expand


    Thursday, January 23, 2020 2:59 PM
  • Hello Aleksei,

    It looks promising, but there is a little problem: The rows are rearranged.

    Is there any way to keep the original order OR add an index column before (which is also rearranged)?

    Andreas.

    Background:

    As usual in real life my data has much more columns. So my whole idea is to separate and simplify the data from the original data, then fill the balance column, finally use a JOIN and merge the Balance column with the original data.

    That way is simple and can be easely adapted to different tasks (I have some...)

    Friday, January 24, 2020 5:24 AM
  • More flexible solution:

    let
        Source = Excel.CurrentWorkbook(){[Name="Order"]}[Content],
        i = Table.AddIndexColumn(Source, "i"),
        group = Table.Group(i, {"ID"}, {"temp", each
                let
                    rec = List.Buffer(Table.ToRecords(_)),
                    stock = try Table.Join([[ID]], "ID", Stock, "ID")[Amount]{0} otherwise 0
                in
                    List.Transform(List.Zip({rec, List.Generate(()=>[i=0, amt = rec{i}[Amount], st = List.Max({stock-amt,0}), bal = List.Min({stock,amt})],
                                                        each [i] < List.Count(rec),
                                                        each [i = [i]+1, amt = rec{i}[Amount], st = List.Max({[st]-amt,0}), bal = List.Min({[st],amt})],
                                                        each [[bal]])}), Record.Combine)}),
        toTable = Table.FromRecords(List.Combine(group[temp])),
        final = Table.RemoveColumns(Table.Sort(toTable,{"i", 0}),{"i"})
    in
        final


    Friday, January 24, 2020 9:40 AM
  • Hello Aleksei,

    That's really magnificent! Thank your very much.

    Now I have to test the solution with my real data during the next week. I’ll leave this task open till I'm done.

    If anyone else has another idea, please do not hesitate to post.

    Thank you again for your great support.

    Andreas.

    Friday, January 24, 2020 11:00 AM
  • Hello Aleksei,

    In practice, it is too time-consuming to insert and change the code, so I had the idea to create a custom function.
    But somewhere I'm making a mistake and I just don't know where.

    Sample file:
    https://www.dropbox.com/s/9d44i2t8ti24i94/Test_MergeAndBalance.xlsx?dl=1

    (Stock as table, StockID as text, StockAmount as text, Source as table, SourceID as text, SourceAmount as text) =>
    let
        i = Table.AddIndexColumn(Source, "i"),
        group = Table.Group(i, {SourceID}, {"temp", each
                let
                    rec = List.Buffer(Table.ToRecords(_)),
                    stock = try Table.Join([[SourceID]], SourceID, Stock, StockID)[SourceAmount]{0} otherwise 0
                in
                    List.Transform(List.Zip({rec, List.Generate(()=>[i=0, amt = rec{i}[SourceAmount], st = List.Max({stock-amt,0}), bal = List.Min({stock,amt})],
                                                        each [i] < List.Count(rec),
                                                        each [i = [i]+1, amt = rec{i}[SourceAmount], st = List.Max({[st]-amt,0}), bal = List.Min({[st],amt})],
                                                        each [[bal]])}), Record.Combine)}),
        toTable = Table.FromRecords(List.Combine(group[temp])),
        final = Table.RemoveColumns(Table.Sort(toTable,{"i", 0}),{"i"})
    in
        final

    Would you please be so kind and look at that?

    Andreas.

    Monday, January 27, 2020 7:00 AM
  • Hi Andreas,

    This code should work:

    (Stock as table, StockID as text, StockAmount as text, Source as table, SourceID as text, SourceAmount as text) =>
    let
        i = Table.AddIndexColumn(Source, "i"),
        group = Table.Group(i, SourceID, {"temp", each
                let
                    rec = List.Buffer(Table.ToRecords(_)),
                    stock = try Table.Column(Table.Join(Table.SelectColumns(_, SourceID), SourceID, Stock, StockID), StockAmount){0} otherwise 0
                in
                    List.Transform(List.Zip({rec, List.Generate(()=>[i=0, amt = Record.Field(rec{i}, SourceAmount), st = List.Max({stock-amt,0}), bal = List.Min({stock,amt})],
                                                        each [i] < List.Count(rec),
                                                        each [i = [i]+1, amt = Record.Field(rec{i}, SourceAmount), st = List.Max({[st]-amt,0}), bal = List.Min({[st],amt})],
                                                        each [[bal]])}), Record.Combine)}),
        toTable = Table.FromRecords(List.Combine(group[temp])),
        final = Table.RemoveColumns(Table.Sort(toTable,{"i", 0}),{"i"})
    in
        final
    Assumed, there is single key column. Otherwise, the code should be slightly different.
    Monday, January 27, 2020 10:26 AM
  • Hi Aleksei,

    it works perfectly, thank you very much for your help.

    Andreas.

    Friday, January 31, 2020 5:22 AM