Power Query/M conditional row sums based on previous rows

[jussivi]

I have a data set containing dates and amounts for upcoming, cash flow causing accounts payable and accounts receivable transactions. Those are exported from accounting software and I use them for cash flow forecast. Other transactions are added manually to the forecast.

Problem with those AP-/AR-transactions is that they have arbitrary due dates on bank holidays or weekends and those amounts must be moved to the next upcoming banking day.

Is it possible to calculate so called “corrected banking day amount” -column in PQ where new amount for any bank holidays is zero and any possible original amounts are added to the next banking day amount? My current experience with M or custom queries is very limited but I am willing to learn.

Logic: IF Date = “not banking day" THEN conditional sum should be zero. IF Date = “banking day” THEN show amount where original row amount and amounts from any preceding bank holidays are summed. Positive amounts are cash inflow and negative amounts cash outflow.

Example:

[TABLE="width: 490"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Amount[/TD]
[TD]Type[/TD]
[TD]BankingDay?[/TD]
[TD]“CorrectedBankingDayAmount”[/TD]
[/TR]
[TR]
[TD]26.3.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]27.3.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]28.3.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]29.3.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]30.3.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]NO[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31.3.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]NO[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]NO[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]NO[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]4.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]5.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]6.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]7.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]NO[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8.4.2018[/TD]
[TD]-500[/TD]
[TD]AR[/TD]
[TD]NO[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]-300[/TD]
[/TR]
[TR]
[TD]10.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]12.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]13.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]YES[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]14.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]NO[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15.4.2018[/TD]
[TD]100[/TD]
[TD]AR[/TD]
[TD]NO[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Goal is to have this table and other table containing manual transactions appended with PQ’s “append queries” feature.

I would be thankful for any help or insights!

 

[billszysz]

Assuming your data is in Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", type number}, {"Type", type text}, {"BankingDay?", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "ToGroup", each if [#"BankingDay?"] = "YES" then [Index] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"ToGroup"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"ToGroup"}, {{"CorrectedBankingDayAmount", each List.Sum([Amount]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([ToGroup] <> null)),
    #"Merged Queries" = Table.NestedJoin(#"Filled Up",{"Index"},#"Filtered Rows",{"ToGroup"},"Almost",JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Almost", {"CorrectedBankingDayAmount"}, {"CorrectedBankingDayAmount"}),
    #"Sorted Rows" = Table.Sort(#"Expanded {0}",{{"Index", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,0,Replacer.ReplaceValue,{"CorrectedBankingDayAmount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Index", "ToGroup"})
in
    #"Removed Columns"

Is that what you need?

 

[jussivi]

Thank you for your neat solution! Seems to work perfectly. Now I just have to figure out what really happens there in the code.