Forum Discussion

Cwarnock's avatar
Cwarnock
New Member
4 years ago
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. W...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago
    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"

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
4 years ago
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"