Forum Discussion
Solved
Count of consecutive unbroken value
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?
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"
2 Replies
CNENFRNLCommunity Champion
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"
ronrsnfldSuper User
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"