How To Clear Domain_4pany Balances

Hi all,

I've spent my last week exploring the dephts of Power Query, aiming to process accounting data for a nice stacked visualization of the developments on some bank and liabilitity accounts:

This video tutorial by @MarcelBeug helped me a great deal in achieving this. Starting from a table with an "Inflow" and an "Outflow" value for each transaction, I could calculate the running totals / "Balance" values for each "Account".

As I also needed a "Balance" for each "Date" with no bookings, this is the code I ended up with:

// Calculate Balance (Translated to English)      OnlyBalanceAccounts = Table.SelectRows(Input, each (Number.FromText(Text.At([Account Number], 0)) < 3)),     WithNetFlow = Table.AddColumn(OnlyBalanceAccounts, "Net Flow", each (if Number.FromText(Text.At([Account Number], 0)) = 1 then [Inflow] - [Outflow] else 0) + (if Number.FromText(Text.At([Account Number], 0)) = 2 then [Outflow] - [Inflow] else 0), type number),      OnlyDateColumn = OnlyBalanceAccounts[Date],     FirstDate = List.Min(OnlyDateColumn),     LastDate = List.Max(OnlyDateColumn),     NumberOfDates = Number.From(LastDate - FirstDate) +1,     EachDate = List.Dates(FirstDate, NumberOfDates, #duration(1, 0, 0, 0)),     EachDateTable = Table.FromColumns({EachDate},{"Date"}),      OnlyAccountColumn = OnlyBalanceAccounts[Account Number],     WithoutDuplicates = List.Distinct(OnlyAccountColumn),     EachAccountTable = Table.FromColumns({WithoutDuplicates},{"Account Number"}),      CombinedTable = Table.AddColumn(EachAccountTable, "Date", each EachDateTable),     ExpandedDatesXAccounts = Table.ExpandTableColumn(CombinedTable, "Date", {"Date"}, {"Date"}),      Stacked = Table.Combine({WithNetFlow , ExpandedDatesXAccounts}),     Sorted = Table.Buffer(Table.Sort(Stacked,{{"Account Number", Order.Ascending}, {"Date", Order.Ascending}, {"OriginalID", Order.Ascending}})),      GroupedWithFunction = Table.Group(Sorted, {"Account Number"}, {{"Tables", AddBalance, Value.Type(AddBalance(Sorted))}}),     Expanded = Table.ExpandTableColumn(GroupedWithFunction, "Tables", {"OriginalID", "Date", "Reference", "Booking Description", "Document Link", "Business Partner", "Project", "Item Description", "Other Stuff", "Currency", "FX Rate", "Tag is ""Local""", "VAT Category", "Booking Type is ""Reset""", "Inflow", "Outflow", "Net Flow", "Balance"}, {"OriginalID", "Date", "Reference", "Booking Description", "Document Link", "Business Partner", "Project", "Item Description", "Other Stuff", "Currency", "FX Rate", "Tag is ""Local""", "VAT Category", "Booking Type is ""Reset""", "Inflow", "Outflow", "Net Flow", "Balance"}),     Cleaned = Table.RemoveColumns(Expanded ,{"Net Flow"}),      OnlyPLAccounts = Table.SelectRows(Input, each (Number.FromText(Text.At([Account Number], 0)) >= 3)),     AllAccounts = Table.Combine({Cleaned, OnlyPLAccounts})

And the function for the "Balance" calculation:

(Table as table) as table => let     Source = Table,     NetFlows = List.Buffer(Source[Net Flow]),     Custom = Source,     WithIndex = Table.AddIndexColumn(Custom, "Index", 1, 1),     WithBalance = Table.AddColumn(WithIndex, "Balance", each Number.Round(List.Sum(List.FirstN(NetFlows,[Index])),2), type number),     Cleaned = Table.RemoveColumns(WithBalance,{"Index"}) in     Cleaned

However, there are some things that I didnt'get:

Would this be achievable without using a function, theoretically? I've tried this for quite some time, but didn't find the right way to refer to the "Net Flow" column in the nested tables (all grouped by "Account") for the List.Sum function (as a list I guess).
How could we filter the redundant dates from ExpandedDatesXAccounts, meaning those combinations of Date and Account for which we actually have at least one transaction in the source data?
Can we calculate the "Balance" only for the last transaction (Max "OriginalIID") of each "Date"? This would improve visualization in PowerBI - so far I got to live with "Average" as aggregation, while each date's end balance per account is the only thing investors about.

Grateful for everything I can learn from you!

yours,
Luke