To get cumulative count in time series, by occurrence of 'TRUE' in a boolean column of the data by category

user_7004463 0
Viewed 142

I have time series data, in which a repeatable event is marked as "TRUE" in a boolean type column. I need to plot a column chart, in which I will plot the duration of the event every time it occurs. So I just need to create a serial number of event occurrence. Also, this has to be done over each category variable. In below data Event_Boolean_Var is to be used to get a cumulative count every time TRUE occurs and create Event Number column. I tried conditional column and new quick measure; I am not able to find a similar question on research

 Time_index Category_Var    Event_Boolean_Var   Event Number    Duration
11/14/2010 0:00 A   FALSE       0.0166667
11/14/2010 0:01 A   TRUE    1   0.0166667
11/14/2010 0:02 A   TRUE    1   0.0166667
11/14/2010 0:03 A   FALSE       0.0166667
11/14/2010 0:04 A   FALSE       0.0166667
11/14/2010 0:05 A   FALSE       0.0166667
11/14/2010 0:06 A   FALSE       0.0166667
11/14/2010 0:07 A   FALSE       0.0166667
11/14/2010 0:08 A   TRUE    2   0.0166667
11/14/2010 0:09 A   TRUE    2   0.0166667
11/14/2010 0:10 A   TRUE    2   0.0166667
11/14/2010 0:11 B   FALSE       0.0166667
11/14/2010 0:12 B   FALSE       0.0166667
11/14/2010 0:13 B   FALSE       0.0166667
11/14/2010 0:14 B   TRUE    3   0.0166667
11/14/2010 0:15 B   TRUE    3   0.0166667
11/14/2010 0:16 B   TRUE    3   0.0166667
11/14/2010 0:17 B   TRUE    3   0.0166667
11/14/2010 0:18 B   FALSE       0.0166667

EDIT Above is sample data, real data is from a datalake combining a bunch of parquet files. Need help in figuring out Data object in below answer:

let
    Source = AzureStorage.DataLake("https://........."),

    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),

    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),

    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"TIME_INDEX", type datetime}, ...}),
    .
    .
    #"Added Index" = Table.AddIndexColumn(#"Changed Type4", "Index.1", 1, 1, Int64.Type),  
  
    boole = Table[Event_Boolean_Var],
    len_boole = List.Count(boole),
    CustomCol = List.Generate(
            () => [curr = false, i = 0, n = 0],
            each [i] < len_boole,
            each [curr = boole{[i] + 1}, i = [i] + 1,
                n = if curr = true and [curr] = false then [n] + 1 else [n]],
            each if [curr] = true then [n] else null
        ),
    AddCol = Table.FromColumns(
      List.Combine({Table.ToColumns(Table), {CustomCol}}),
      List.Combine({Table.ColumnNames(Table), {"Event Number"}})
    )
 in
  AddCol
dax powerbi powerquery
2 Answers

It's a bit more complex than calculating the value for each row separately, but it's also much more computationally efficient to generate your custom column as a list and add then add that list as a new table column.

let
  Data = < Data Source or Previous Step here >,
  boole = Data[Event_Boolean_Var],
  len_boole = List.Count(boole),
  CustomCol = List.Generate(
    () => [curr = false, i = 0, n = 0],
    each [i] < len_boole,
    each [curr = boole{[i] + 1}, i = [i] + 1,
          n = if curr = true and [curr] = false then [n] + 1 else [n]],
    each if [curr] = true then [n] else null
  ),
  AddCol = Table.FromColumns(
    List.Combine({Table.ToColumns(Data), {CustomCol}}),
    List.Combine({Table.ColumnNames(Data), {"Event Number"}})
  )
in
  AddCol
user_7652260

Click on Event column ... transform ... data type .. true/false

Add column .. index column...

Add Column .. custom column ... with formula

= (i)=>Table.RowCount(Table.SelectRows(#"Added Index", each [Category_Var]=i[Category_Var] and [Event]=true and [Index]<=i[Index]))

sample full code:

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type logical}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index","Event Number",(i)=>Table.RowCount(Table.SelectRows(#"Added Index", each [Category_Var]=i[Category_Var] and [Event]=true and [Index]<=i[Index])), type number )
in #"Added Custom"
user_92642300
Related