Count of consecutive unbroken value

[Cwarnock]

Hay guys we are trying to track the average periods a client is active as well as the duration of each of those periods. I.E. if a client were actvie from Jan-Mar and they were also active in July. We want to be able to have a report that shows this client had two periods of activity one that lasted 3 months and one that only lasted 1 month. I have been able to create a graph that displays a 1 if the client was active and 0 if they were not. The issue I'm having is I need to preform a count of unbroken repeated values. In the screenshot below you see we have the value of 1 for Dec 2020 followed by a 0 for Jan and then two back to back 1's in Feb and Mar. I need a calcution that will return a 1 on Dec, a 0 on January, and 2 on both Feb and Mar. Any Ideas? 

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUfJNLErOANIGSrE6cEHHgqLMHHRB38RKIGmILORVmpeKKZaDoc6xNL20uATdwODUgpLU3KTUInTl/skl+ViE/fLLYMpRzHFJTcYwxhDkksS80sSiSmTlIGG31KQiqDiKclhIoAhihIQhPCRQhKAhARSLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, Active = _t]),
    #"Grouped Rows" = Table.Group(Source, "Active", {"grp", each _}, 0),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "CHK", let l= #"Added Index"[Active] in each if [Active]="1" then List.Count(List.Select(List.FirstN(l,[Index]), each _="1")) else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Active", "Index"}),
    #"Expanded grp" = Table.ExpandTableColumn(#"Removed Columns", "grp", {"Year", "Month", "Active"}, {"Year", "Month", "Active"})
in
    #"Expanded grp"

[ronrsnfld]

You can do this by

• Add a column that is a copy of the "Activity" column but shifted down by one
• Add an Index column
• Add a custom "grouper" column that detects the continuation by the fact that both the Activity column and the shifted column are both "1".  If they are, then return a null, else return the Index.
• Fill Down the grouper column
• Groupby the grouper column and return the row count if the activities are 1, else return a 0

let

//This is just one way to create the table
   monthNames = 
    List.Transform(
        List.Generate(
            ()=>[y=2020, m=#datetime(2020,3,1,0,0,0),idx=0],
            each [idx]<16,
            each [y=Date.Year(Date.AddMonths([m],1)), m=Date.AddMonths([m],1), idx=[idx]+1],
            each {[y],[m]}),
        each Text.From(_{0}) & ";" & Date.MonthName(_{1})),
    vals = {0,0,0,0,0,0,0,0,0,1,0,1,1,0,0,0},
    tbl = Table.FromColumns({monthNames,vals},
        type table[ym=text, Active = Int64.Type]),

    Source = Table.SplitColumn(tbl, "ym", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Year", "Month"}),

//"real code" starts here
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", type text}}),

//Add "shifted" active column
    shiftActive = Table.FromColumns(
            Table.ToColumns(#"Changed Type") &
              {{null} & List.RemoveLastN(#"Changed Type"[Active],1) },
              type table[Year=Int64.Type, Month=text, Active=Int64.Type, shiftedActive = Int64.Type]),
    #"Added Index" = Table.AddIndexColumn(shiftActive, "Index", 0, 1, Int64.Type),

//custom column to detect the runs by comparing current with previous row
    #"Added Custom" = Table.AddColumn(#"Added Index", "grouper", 
        each if [Active] = 0 then [Index] 
            else if [Active] = 1 and [shiftedActive] = 0 then [Index] 
            else null),

//remove uneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"shiftedActive", "Index"}),

//fill down the grouper column
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"grouper"}),

//group rows by the grouper

    #"Grouped Rows" = Table.Group(#"Filled Down", {"grouper"}, {
        {"all", each _, type table[Year=Int64.Type, Month=text, Active=Int64.Type]},

//this line is where the magic comes from
        {"Run", each if List.ContainsAll(_[Active],{1}) then Table.RowCount(_) else 0, Int64.Type}}),

//remove the grouper column and re-expand the table
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"grouper"}),
    #"Expanded all" = Table.ExpandTableColumn(#"Removed Columns1", "all", {"Year", "Month", "Active"}, {"Year", "Month", "Active"})
in
    #"Expanded all"