Help With Counting Occurrences in Date Period

Hi russell80,

Sorry, re-wrote my post as I found a much better solution.

This is a very intersting problem. The way to improve it is to somehow reduce the number of iterations and the size of the data being manipulated with.

The code below runs against 20k lines in about 3 sec on my laptop, which is quite new, so I guess that this is somewhere near the in-cloud performance. This goes more or less in line with wdx223_Daniel but uses grouping to reduce the number of the rows we have to deal in each particular period of time.

Ignore anything before f  this steps are used to set up the scene for testing.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcVJDQAwCARAL7yrgjuAA4J/G012PrNLzEz3lkQEqyo2M+zuOCJwZuKqwt2NZ4buPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    Dates = List.Dates(#date(2018,1,1), 2000, #duration(1, 0,0,0)),
    Custom1 = Table.AddColumn(#"Changed Type", "Date", each Dates),
    #"Expanded Date" = Table.ExpandListColumn(Custom1, "Date"),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Date",{"Date", "Name"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}}),
    
    f = (t as table) as table =>
        let 
            mList = List.Buffer(t[Date]),
            out = List.Accumulate(mList, {}, (a, n)=> 
                let
                    limit = Date.AddMonths(n, -12), 
                    out = a & {[Date = n, Count = 1 + List.Count(List.Select(mList, each _ < n and _ > limit))]}
                in out)
        in Table.FromRecords(out),
    
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Name"}, {{"Data",  f}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Count"}, {"Date", "Count"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Data",{{"Date", type date}})
in
    #"Changed Type2"

Cheers,

John

Cheers,

John