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
It would be easier to help you with a text data sample (or workbook) that could be pasted (or downloaded).
Hi ronrsnfld,
https://1drv.ms/x/s!AsCcZ86I6rZpgYMia0uQfVaxB0zXcw
Thank you for your time and suggestion. I have created a full sample workbook that I should be able to provide all of the data structure and associated queries. I have added a few extra queries that breakdown the results for each case so that I could add a query that shows the desired outcome I need once the Table.Group loop can be created (assuming someone is hopefully able to find a solution). The link to this workbook is provided above.
An alternative approach I thought of, but still do not have enough M code experience to pull off, is to take the list from ActivityRangeSAMPLE[Period], then use possibly even a nested List.Accumulate within List.Accumulate (if this is even possible, or a combination of List.Generate and List.Accumulate, or List.Sum and List.Accumulate) to sum any values associated with each corresponding quarter, and loop that to create a column of corresponding sum values for each case. Hopefully that makes sense.
I hope that this sample workbook that I created will be more helpful as suggested. Of course, please follow up with me at any time with any questions, suggestions, or concerns that you might have.
Thank you.
Best Regards