Knighthawk
4 years agoHelper I
Power Query-Repeat/Loop Table.Group/List.Average function process for each column (List.Accumulate)
Hello, I am working on a project in Power Query that requires me to determine the average quarterly balance for each case number. I have most of the code (although probably not the most efficien...
- 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