Forum Discussion

Knighthawk's avatar
Knighthawk
Helper I
4 years ago
Solved

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...
  • ronrsnfld's avatar
    ronrsnfld
    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