Forum Discussion

Calva's avatar
Calva
New Member
4 years ago
Solved

Runnig totals that reset by value changes on other column

Hello,

I need help to calculate how long (seconds) a change of a parameter (column Presenca_Skid_RB02) lasts (updating every second). My original table is this:

I've got a calendar with date and time increasing by 1 second. The parameters may get 0 or 1, it dependes on the conditions of the machine over time. I need to get the exact timestamp after 120 seconds that the parameter went to "1", and, the exact timestamp after 10 seconds it went to "0".... repeatdly.

 

I tried to count an index for 0 and 1 as follows:

 

= Table.Group(#"Linhas Filtradas1", {"Presenca_Skd_RB02"}, {{"Contagem", each Table.AddIndexColumn(_,"Index",1,1), type table }})

 

After expanded the result is: 

It counts the indexes separatedly for "0" and "1". It works fine, BUT i need it to reset the count for each change. Ex: In the picture, when it changed from 1 to 0 the index went to "1" (good) but when the value gets back to 1 I need it to index "1" again, not continue the old index "457".

 

I hope I've been clear on my needs. Better if the solution is on PowerQuery!

Need Help
Tips and Tricks
Tutorial Requests
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I answered a similar question here:

    https://community.powerbi.com/t5/Power-Query/How-to-count-sequence-per-group/m-p/2222977

     

    Here's a cleaner example query though:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7RCcAwCATQXfwO6Gkj7Swh+6/RJLSk2k8f3J2tEagQGKwiQr28oBGUbYMs8AxIGeOaAdcWXWKIUvnIAI81zj/Qz7QNOcMzUyDjfqjf", BinaryEncoding.Base64), Compression.Deflate)), type table [ID = Int64.Type, Date = Date.Type]),
    T = Table.Buffer(Table.AddIndexColumn(Source, "Order", 0, 1, Int64.Type)),
    AddIndex = Table.FromRecords(
        List.Generate(
            () => [Order = 0, Index = 1],
            each [Order] < Table.RowCount(T),
            each [
                    Order = [Order] + 1,
                    Index = if T{Order}[ID] = T{[Order]}[ID] then [Index] + 1 else 1
                ]
        ),
        type table [Order = Int64.Type, Index = Int64.Type]
    ),
    #"Merged Queries" = Table.NestedJoin(T, {"Order"}, AddIndex, {"Order"}, "Index", JoinKind.LeftOuter),
    #"Expanded NewID" = Table.ExpandTableColumn(#"Merged Queries", "Index", {"Index"}, {"Index"})
in
    #"Expanded NewID"

     

    This query ^^ starts with the left two columns and adds the other two.

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    I answered a similar question here:

    https://community.powerbi.com/t5/Power-Query/How-to-count-sequence-per-group/m-p/2222977

     

    Here's a cleaner example query though:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc7RCcAwCATQXfwO6Gkj7Swh+6/RJLSk2k8f3J2tEagQGKwiQr28oBGUbYMs8AxIGeOaAdcWXWKIUvnIAI81zj/Qz7QNOcMzUyDjfqjf", BinaryEncoding.Base64), Compression.Deflate)), type table [ID = Int64.Type, Date = Date.Type]),
    T = Table.Buffer(Table.AddIndexColumn(Source, "Order", 0, 1, Int64.Type)),
    AddIndex = Table.FromRecords(
        List.Generate(
            () => [Order = 0, Index = 1],
            each [Order] < Table.RowCount(T),
            each [
                    Order = [Order] + 1,
                    Index = if T{Order}[ID] = T{[Order]}[ID] then [Index] + 1 else 1
                ]
        ),
        type table [Order = Int64.Type, Index = Int64.Type]
    ),
    #"Merged Queries" = Table.NestedJoin(T, {"Order"}, AddIndex, {"Order"}, "Index", JoinKind.LeftOuter),
    #"Expanded NewID" = Table.ExpandTableColumn(#"Merged Queries", "Index", {"Index"}, {"Index"})
in
    #"Expanded NewID"

     

    This query ^^ starts with the left two columns and adds the other two.