Forum Discussion
Power Query-Repeat/Loop Table.Group/List.Average function process for each column (List.Accumulate)
- 4 years ago
Here is code that creates the output table you show as your desired output.
Brief Algorhythm
- Create a List of all the quarter-start dates from the earliest and latest dates in the data set
- Group the data set by CaseId. Then, for each CaseID
- Add rows with all the quarter-start dates in the RunDate column
- Sort by Run Date
- Group by RunDate in order to combine multiple actions on the same date
- Determine Starting Balance as the earliest EndBalance (-/+ any credits/debits on that same day)
- After adding a quarter-start-date column, group by that
- Determine the number of days at each balance level
- calculate a "weighted average" of the balance where the "weight" is the number of days at each level
- Re-expand, add a quarter-year text string and also a sorting column
- Sort into quarter-year order (so the columns will come out in the correct order
- Pivot on the quarter-year text string
See the code comments for better understanding of the algorhythm
fnAllQuarters
//rename fnAllQuarters (L as list)=> let Source = L, dtStart=Date.StartOfQuarter(List.Min(Source)), dtEnd= Date.EndOfQuarter(List.Max(Source)), allQtrs = List.Generate( ()=>[q=dtStart, idx=0], each [q] < dtEnd, each [q=Date.AddQuarters(dtStart,[idx]+1), idx=[idx]+1], each [q] ) in List.Transform(allQtrs, each Date.From(_))***fnDaily Balance***
//Rename `fnDaily Balance` (tbl as table, allQtrs as list)=> let Source = tbl, // allQtrs = fnAllQuarters({#date(2020,12,31), #date(2022,4,2)}), //Group by run date to combine multiple entries on same date into one // Also combine the credit/debit columns into a single entry #"Grouped Rows" = Table.Group(Source, {"RunDate"}, { {"CaseId", each [CaseId]{0}, type text}, {"QSD", each Date.StartOfQuarter([RunDate]{0}), type date}, {"EndBalance", each List.Sum([EndBalance]), Currency.Type}, {"Net Change", each List.Sum([Credit]) + List.Sum([CreditAdj]) - List.Sum([Debit]) - List.Sum([DebitAdj]), Currency.Type}}), //add all quarter start dates to the RunDate Column //then sort by RunDate #"Quarter Start Dates" = let qsd = List.Buffer(allQtrs), colHdrs = Table.ColumnNames(#"Grouped Rows"), rws = List.Accumulate(qsd, {}, (state, current)=> state & {Record.FromList({current} & {#"Grouped Rows"{0}[CaseId]} & {current}, List.FirstN(colHdrs,3))}) in Table.FromRecords(rws, type table[CaseId=text, RunDate=date, QSD=date]), #"Add QSD" =Table.Combine({#"Grouped Rows",#"Quarter Start Dates"}), #"Sorted Rows" = Table.Sort(#"Add QSD",{{"RunDate", Order.Ascending}}), //Starting balance will be the first "EndBalance" - "Net Change" // might be zero #"Starting Balance" = let firstEndingBalance = List.First(List.RemoveNulls(#"Sorted Rows"[EndBalance])), posFirstEndingBalance = List.PositionOf(#"Sorted Rows"[EndBalance],firstEndingBalance,Occurrence.First), firstNet = #"Sorted Rows"[Net Change]{posFirstEndingBalance} in firstEndingBalance-firstNet, //Replace Nulls with 0's to avoid math problems #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,0,Replacer.ReplaceValue,{"EndBalance", "Net Change"}), //Add Running Balance column #"Running Balance" = Table.FromColumns( Table.ToColumns(#"Replaced Value") & { List.Generate( ()=>[rb=#"Starting Balance", idx=0], each [idx] < Table.RowCount(#"Replaced Value"), each [rb = [rb] + #"Replaced Value"[Net Change]{[idx]+1}, idx = [idx]+1], each [rb])}, type table[RunDate=date, CaseId=text, QSD=date, EndBalance=Currency.Type, Net Change=Currency.Type, Running Balance=Currency.Type]), #"Removed Columns1" = Table.RemoveColumns(#"Running Balance",{"EndBalance", "Net Change"}), //Group by quarterly start date to compute Average Daily Balance by quarter //Daily balance is computed by computing a weighted average balance, with the weight // being the number of days spent at each value, and dividing by the number of days in the quarter #"Grouped Rows1" = Table.Group(#"Removed Columns1", {"QSD"}, { {"Avg Daily Balance", (t)=> let #"Offset Date" = Table.FromColumns( Table.ToColumns(t) & {List.RemoveFirstN(t[RunDate],1) & {Date.AddDays(Date.EndOfQuarter(t[QSD]{0}),1)}}, Table.ColumnNames(t) & {"Offset Date"}), #"Days at Balance" = Table.AddColumn(#"Offset Date","Days at Balance", each Duration.Days([Offset Date]-[RunDate])), #"Days x Balance" = Table.AddColumn(#"Days at Balance","Product", each [Running Balance] * [Days at Balance]), AVDB = List.Sum(#"Days x Balance"[Product]) / List.Sum(#"Days x Balance"[Days at Balance]) in AVDB, Currency.Type} }) in #"Grouped Rows1"Main Code
let //change next line to reflect your actual data source Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content], //set data types // I used Currency.Type for the numeric columns as it seems more appropriate. Change it if needed #"Changed Type" = Table.TransformColumnTypes(Source,{{"CaseId", type text}, {"RunDate", type date}, {"Credit", Currency.Type}, {"CreditAdj", Currency.Type}, {"Debit", Currency.Type}, {"DebitAdj", Currency.Type}, {"EndBalance", Currency.Type}}), //get list of all Quarters for aggregation below allQtrs = fnAllQuarters(Source[RunDate]), //Group by CaseID //Aggregate using custom function to compute average daily balances #"Grouped Rows" = Table.Group(#"Changed Type", {"CaseId"}, { {"Average Daily Balance", each #"fnDaily Balance"(_, allQtrs)} }), //Expand the sub tables //Add a column with the qtr-year text strings #"Expanded Average Daily Balance" = Table.ExpandTableColumn(#"Grouped Rows", "Average Daily Balance", {"QSD", "Avg Daily Balance"}), #"Added Custom" = Table.AddColumn(#"Expanded Average Daily Balance", "Qtr-Year", each let q = Number.Mod(Date.QuarterOfYear([QSD])+1,4)+1, yrs = if q < 3 then Text.From(Date.Year([QSD])) & "-" & Text.From(Date.Year([QSD])+1) else Text.From(Date.Year([QSD])-1) & "-" & Text.From(Date.Year([QSD])) in Number.ToText(q,"Q0 ") & yrs, type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"QSD"}), //add column to sort by the qtr-yr date strings // so the pivot will have the columns in desired order // then sort and remove the columns #"Added Custom1" = Table.AddColumn(#"Removed Columns", "sort by qtr-yr", each let x = Text.SplitAny([#"Qtr-Year"],"Q -") in x{2} & x{1}), #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"sort by qtr-yr", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"sort by qtr-yr"}), //Pivot on the qtr-yr string #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Qtr-Year"]), "Qtr-Year", "Avg Daily Balance"), //set the data types #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column", List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Pivoted Column")), each {_, Currency.Type})) in #"Changed Type1"Results from data set in shared workbook
Hi ronrsnfld,
I am guessing that you posted your reply, noticed an issue with the code, and removed the reply post. I did happen to capture the code prior to that and test it out. First of all though, thank you so much for all of the time and work you put into building the code that you have so far. I am extremely grateful. That said, I did run into an error when it tried to pull in the table at the #"Grouped Rows" step, after running the #"fnDaily Balance". I am getting the following error:
Expression.Error: The index is outside the bounds of the record.
Details:
Record=Record
Index=1
Unfortunately I cannot stay late at work today to look through the code or work on this further this evening, but I will pick this back up on Monday, if not sooner. I am guessing that maybe you came across this same error as well, and decided to remove the post, so maybe you will have faster success, being more familiar with your code at this point.
Either way, thank you again, and I hope you have a wonderful weekend!
Actually, that was not the reason I pulled the answer. I was dealing with a logic issue that I thought I was not satisfied had been dealt with appropriately.
As a matter of fact, at least with the data you supplied, the code I had posted ran with no errors.