Power Query-Repeat/Loop Table.Group/List.Average function process for each column (List.Accumulate)

[Knighthawk]

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 efficient code) created, but I am stuck at one of the last steps, and hope that someone might be willing and able to assist in any way.

I need to take the averages of all of the balances listed throughout the quarter, for each case.  Each case needs to remain in its own separate column; however, in the end I intend to Transpose the data so that the Quarter periods are in columns, and the case names are listed in rows with the corresponding average balances as part of each case record.

With the code I have now (See code below:  Power Query - CalendarCaseAverages (M code)), I use Table.Group with List.Average to capture the average balances for one single case; however, I need to repeat/loop that process for every case in CaseList[CaseId].  My current code results in essentially what I need, but I have been unable to determine the code needed to accomplish this for all of the cases (See the two screen shots below that show how the resulting code currently displays the data, and how I need it to display the data once I am able to repeat/loop the Table.Group process).

One additional hurdle that I need to be cautious of throughout this coding, is that the case numbers and the number of cases are going to consistently be changing, so the code needs to be dynamic in a way that does not reference hard-coded column names such as case numbers or Quarter period descriptions.  I am referencing other queries as the reference for processes that need the listing of these items (e.g., CaseList[CaseId], ActvityRange).

One of the main issues I continue to run into due to my lack of experience with M code, is how to incorporate a variable in as a column reference ( e.g., List.Average([variable]) ).

I hope that this information and explanation makes sense.  Of course, please ask any questions that you may have.  Thank you in advance for any time you may take to review and assist with this post.

Power Query - CalendarCaseAverages - Current final result following the "in" "QuarterlyAverages" step:

Power Query - CalendarCaseAverages - Desired final result following the "in" "QuarterlyAverages" step:

Power Query - CalendarCaseAverages (M code)

let
    Cases = CaseList[CaseId],
    Source = Table.FromList({"a"}),
    LoopFunction = List.Accumulate({0..List.Count(Cases)-1}, Source, (state, current) => Table.AddColumn(state, Cases{current}, each 

    let
        Source = CalendarCaseActivityTEST{current},
        BufferedValues = List.Buffer(Source),
        RT = fxRunningTotal(BufferedValues)
    in
        RT, type number)),
    #"Renamed Columns" = Table.RenameColumns(LoopFunction,{{"Column1", "Date"}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Renamed Columns"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table","a",Table.ToList(RangeCalendarList),Replacer.ReplaceValue,{"Column2"}),
    #"Extracted Values" = Table.TransformColumns(#"Replaced Value", {"Column2", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)), 
    #"Transposed Table1" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", Int64.Type}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}}),
    BufferedQuarter = Table.Buffer(ActivityRange),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Quarter", 
        (C) => Table.SelectRows(BufferedQuarter, 
        (R) => C[Date] >= (R)[Start of Quarter] and (C)[Date] <= (R)[End of Quarter])),
    #"Expanded Quarter" = Table.ExpandTableColumn(#"Added Conditional Column", "Quarter", {"Period"}, {"Quarter"}),
    ColumnNames = Table.ColumnNames(#"Expanded Quarter"),   
    #"Reordered List" = List.Combine({{"Quarter"},List.FirstN(ColumnNames,List.Count(ColumnNames)-1)}),
    Result = Table.ReorderColumns(#"Expanded Quarter",#"Reordered List"),
    #"Removed Columns" = Table.RemoveColumns(Result,{"Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",List.Transform(Cases, each {_, type number})),
    QuarterlyAverages = Table.Group(#"Changed Type1", {"Quarter"}, {{"A123456", each List.Average([A123456]), type number}})
in
    QuarterlyAverages

Power Query Function - fxRunningTotal (M code)

(values as list) as list =>

let
    RT = List.Generate
    (
        () => [RT = values{0}, counter = 0],
        each [counter] < List.Count(values),
        each [RT = [RT] + values{[counter] + 1}, counter = [counter] + 1],
        each [RT]
    )
in
    RT

Power Query - CalendarCaseAverages (M code), which I have tried to come up with in order to accomplish the repeat/loop of the Table.Group process, but I cannot overcome the issue of referencing the case name from the list even for the first run-through of the Table.Group process.  I am sure that the List.Accumulate process is an even bigger mess, but since I am stuck in the first run-through, I have been unable to get to the actual List.Accumulate part to try and debug it.

...
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",List.Transform(Cases, each {_, type number})),

    Case = CaseList[CaseId]{0},
    SourceTable = Table.Group(#"Changed Type1", {"Quarter"}, {{Case, each List.Average(Table.ToList(Table.SelectColumns(#"Changed Type1", Case))), type number}}),
    SourceList = Record.ToList(Record.RemoveFields(_,{"Quarter"})),
    #"Grouped Rows" = List.Accumulate({0..List.Count(Cases)-1}, SourceList, (state, current) => Table.Group(state, {"Quarter"}, each {{Cases{current}, each List.Average(Cases{current}), type number}}))
type number}})

in
    #"Grouped Rows"

Additional screenshots for reference:

Original formatting of the list of activity affecting the balances:

Power Query - CaseList[CaseId]:

Power Query - ActivityRange - screenshot:

Power Query - CalendarCaseActivity - Original format screenshot:

Power Query - CalendarCaseActivity - Final List of Lists screenshot:

Power Query - CalendarCaseAverages - screenshot of data layout following the "Result" step:

[Knighthawk]

Hello All,

I have come up with a possible approach, but I am stuck on one minor issue, which is how to the List.Accumulate "current" as a column name variable.  For example, [current], but of course the square brackets used for column name references take input literally, preventing a reference variable from being used.  I tried using Record.Field(_, current), but the sytax in this structure is not working correctly.  This comes up with the same output as the Table.Group, but without the extra complexity.

Any assistance would be greatly appreciated.  Thank you in advance for your time and assistance! 🙂

Sidenote:  More efficient buffering will likely be helpful, but I want to be sure I find a solution before I mess with additional steps that may cause unexpected issues.  If you have any suggestions on this front, it would be much appreciated also.

Here is the file link for the updated file, followed by the new query code I have come up with:

https://1drv.ms/x/s!AsCcZ86I6rZpgYMjPRF4h8vsi9HSrw

Power Query - CaseAveragesSAMPLE (M code) :

let
    Source = #"CalendarCaseAveragesSAMPLE",
    #"Added Custom" = Table.FromList(ActivityRangeSAMPLE[Period]),
    LoopSum = Table.AddColumn(#"Added Custom", CaseListSAMPLE[CaseId]{0}, each List.Sum(Table.SelectRows(Source, (Q) => Q[Quarter] = [Column1])[A123456]), type number)

//**Attempting in the following line to turn this into a loop through List.Accumulate, but unsure how to make [current] a viable "current" column name variable**
//    LoopSum = List.Accumulate({0..List.Count(CaseListSAMPLE[CaseId])-1}, #"Added Custom", (state, current) => Table.AddColumn(#"Added Custom", CaseListSAMPLE[CaseId]{current}, each List.Sum(Table.SelectRows(Source, (Q) => Q[Quarter] = [Column1])[current]), type number))

//**Attempted in this next following line to convert [current] into a viable "current" column name variable, but syntax appears to be off somewhere**
//    LoopSum = List.Accumulate({0..List.Count(CaseListSAMPLE[CaseId])-1}, #"Added Custom", (state, current) => Table.AddColumn(#"Added Custom", CaseListSAMPLE[CaseId]{current}, each List.Sum(Table.SelectRows(Source, (Q) => Q[Quarter] = [Column1])Record.Field(_,current)), type number))

//**Without being able to move beyond this issue, I am unsure what additional debugging will be needed to have List.Accumulate work as intended (i.e., create a column of quarterly averages for each Case Id)**

in
    LoopSum

[ronrsnfld]

I'm still a bit confused with all your screen shots.

But it seems to me that if you want to return the Average balance for each quarter by Case ID, there may be a simpler method

• Add a custom column which creates the Quarter-Year string
• Group by Case ID and Quarter-Year string
  • Aggegate -- Average of End Balance
• Add a column for proper sorting of the Quarter-year string
  • I used the numeric yyyyq
• Pivot on the Quarter column
  • No aggregation

Given the data you supplied in that workbook, this results in:

Read the code comments for better understanding

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}}),

//Creatge Quarterly Period text string
//  assuming yr starts July 1
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Quarter", each 
        let 
            qtr=Number.Mod(Date.QuarterOfYear([RunDate])+1,4)+1,
            yr1=if qtr < 3 then Date.Year([RunDate])
                    else Date.Year([RunDate])-1,
            yr2=if qtr  then Date.Year([RunDate]) + 1 
                else Date.Year([RunDate])
        in 
            "Q" & Text.From(qtr) & Number.ToText(yr1," 0-") & Text.From(yr2)),

//Group by CaseID and quarter
// Aggregate by average of end balance
    #"Grouped Rows" = Table.Group(#"Added Custom", {"CaseId", "Quarter"}, {
        {"Average", each List.Average([EndBalance]), Currency.Type}}),
    
//add column for proper sorting of Quarters
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Quarterly Sort Value", each 
        let x=Text.SplitAny([Quarter],"Q -")
        in x{2} & x{1}),

//sort by quarter, then remove sorting column
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Quarterly Sort Value", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Quarterly Sort Value"}),

//Pivot on Quarter with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Quarter]), "Quarter", "Average")
in
    #"Pivoted Column"

[Knighthawk]

Hi ronrsnfld,

Thank you so much for taking the time to put together this code recommendation (it did teach me some valuable tricks that I was not yet aware of).  I did need to make one minor adjustment, as it seems that the "< 3" was removed for yr2 in the #"Added Custom" step, so I just added that back in and the code worked as you explained.  However, I believe that your code is taking the average of the transactions and not the average daily balance, which is what I need for this project.  

There is likely going to be some potential for me to consolidate some of these queries and steps once I have the final coding issues figured out, but at the moment the primary reason for some of the additional queries are to properly place the transactions taken from the orginal transaction list into the corresponding dates across a full date listing.  The special called function (fxRunningTotalSAMPLE) runs through the list of transactions, starting with the beginning balance, and keeps a running total for each day.

For example, if the starting balance is $50 on 12/31/2020, then on 01/15/2021 $25 was taken out, then on 04/20/2021 the last $25 was taken out, $50 would show on each day through 01/14/2021, then would show $25 from 1/15/2021 through 04/14/2021, then starting 04/20/2021 it would show a $0 balance.  This is all needed in order to add up the balance for all of the days within the quarter, then divide by the number of days within the quarter.

$50 * 14 days = $700,

$25 * 76 days = $1900,

$700 + $1,900 = $2,600,

$2,600 / 90 days in the quarter 3 = $28.88888... (average daily balance for quarter 3)

$25 * 20 days = $500,

$0 * 71 days = $0,

$500 + $0 = $500,

$500 / 92 days in the quarter 4 = $5.43478... (average daily balance for quarter 4)

If I were to take the average of only the transactions as it appears the code you provided would do, the result for quarter 3 would instead be $12.50 ( ($50 - $25) / 2 ) and -$25 ( $25 / 1 ) for quarter 4.  I hope this helps to explain the difference and why the extra steps are necessary (at least based upon my limited knowledge of M code at this point).

The result that should show at the end can be seen on the "DesiredOutcomeSAMPLE" query, from which I have provided the following screenshot:

I am not sure if you noticed that I came up with a more simple possible approach and posted it as a reply on here yesterday afternoon, but I am still stuck at a spot of trying to determine the proper coding that can be used in the List.Accumulate loop.

Thank you again for the time that you have taken to assist with this already and for your recommendation, I truly appreciate it and did learn some valuable tricks that I was not aware of yet.  If you happen to have any solutions for I hope to be the last touches on the code that I posted yesterday afternoon, it would be an enormous help!

Also, I apologize if the screenshots that I have provided are confusing.  The intent is to provide screenshots of how each of the primary queries look, for those individuals that would rather have a quick visual instead of downloading the file and going through each of the queries to see each of the individual query results.

Best Regards

[ronrsnfld]

I don't know why the system removed that second `<3`.  It was present in the code I pasted and I've not seen that behavior in the past.  Hmmm:  In the deleted it was present as `<3` and in the retained present as `< 3`.  Maybe that's the difference.

Average daily balance per quarter is, I think, a bit different from what I first understood you wanted.  I will post a modification -- it can be done as part of the Table.Group aggregation.

[Knighthawk]

Thank you ronrsnfld for being willing to continue trying to assist with this.  I appreciate any suggestions you can provide to help with finalizing/debugging either the first or second appraoches that I posted, or a different approach that you feel might also work.

[ronrsnfld]

I've been busy and haven't been able to spend as much time as I wanted. However, I am working on a on an approach that should execute more quickly than the one you had on your shared workbook.

[Knighthawk]

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!

[ronrsnfld · Accepted solution]

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

[Knighthawk]

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"

[ronrsnfld]

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).

[Knighthawk]

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.

[ronrsnfld]

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.

[ronrsnfld]

It would be easier to help you with a text data sample (or workbook) that could be pasted (or downloaded).

[Knighthawk]

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