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!
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
- ronrsnfld4 years ago
Super User
Thank you for the feedback.
It probably won't make a difference in your application, but, just so you know, the Currency data type is precise to 19 significant digits, with always four digits to the right of the decimal (not two). It only displays two in the PQ UI, but, if you load it into Excel, you can see the four decimal digits (assuming the value requires that).
- Knighthawk4 years ago
Helper I
Hi ronrsnfld,
Thank you so much for all of your time, effort, and assistance with this solution. I am very grateful, and truly appreciate it!
I'm not sure if I copied something incorrectly from the first post, but the code I copied from here worked perfectly!
It is going to take me a while to go through to try to understand how all of the code is working together, but I definitely look forward to reviewing it and learning from it.
Just as a sidenote about the value types that you mentioned and set to Currency. Normally the Currency type would be correct; however, since this process is dealing with averages, and includes a constantly accruing balance, I do need to keep the values as decimals, otherwise rounding to the second decimal place would cause issues with the totals and the accumulated values of each case over time, so I did adjust those back.
One final step that I added, which I pieced together from code I found elsewhere, is to add a "Totals" row at the top. I included the final code below for reference in case anyone is interested.
This was such a huge help. Thank you again!
Best Regards
let //change next line to reflect your actual data source Source = MasterActivity, //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", type number}, {"CreditAdj", type number}, // {"Debit", type number}, {"DebitAdj", type number}, {"EndBalance", type number}}), //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(Source, {"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"), //Adds a row at the top for totals Source2 = #"Pivoted Column", List_AllCases = Source2[CaseId], List_QuarterCol = List.RemoveFirstN(Table.ColumnNames(Source2), 1), List_Col = Table.ColumnNames(Source2), List_ColsToAggregate = List.Transform(List_Col, each {_, (x) => List.Sum(Record.Field(x, _)), type number}), Grouping = Table.Group(Source2, {}, List_ColsToAggregate, GroupKind.Local), TabCombine = Table.Combine({Grouping, Source2}), #"Replaced Errors" = Table.ReplaceErrorValues(TabCombine, {{"CaseId", "Totals:"}}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Errors",{{"CaseId", "Case #"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Case #", type text}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type", List.Transform(List_QuarterCol, each {_, type number})) in #"Changed Type1" - Knighthawk4 years ago
Helper I
Thank you, ronrsnfld. I remember learning that at one point now that you mention it, but forgot, so I appreciate the clarification on how Currency type is processed in Power Query. To be on the safe side (just so there isn't a penny issue somewhere along the line with larger sets of data), I am keeping it decimal in Power Query, and then converting it to the Accounting format in Excel as needed.