Count of occurace of a specific text in between rows

[Sunil M P]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ROW
[/TD]
[TD]SESSION
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Application Started
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Application Ended
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Application Ended
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Application Started
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Application Started
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Application Ended
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Application Started
[/TD]
[/TR]
</tbody>[/TABLE]
This is a sample data taken from an External Excel Sheet.

Our motive is to count the occurrence of "Application Ended" in between "Application Started". and return the desired output as below.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]S NO.
[/TD]
[TD]Count of Application Ended between application starts
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

Is it possible to achieve this using Power Query. Please help.

 

[Kazlik]

Do you have any other data such as a unique id for each session?

 

[billszysz]

Of course, it is possible

let
Source = Excel.CurrentWorkbook(){[Name="tblStartEnd"]}[Content],
FilterRows = Table.SelectRows(Source, each ([SESSION] = "Application Started")),
AddedIndex = Table.AddIndexColumn(FilterRows, "Indeks", 0, 1),
AddedColumn = Table.AddColumn(AddedIndex, "Custom", each if [Indeks] = 0 then 0 else AddedIndex{[Indeks]}[ROW] - AddedIndex{[Indeks]-1}[ROW]- 1),
FilterRows2 = Table.SelectRows(AddedColumn, each ([Custom] <> 0)),
RemoveColumns = Table.SelectColumns(FilterRows2,{"Custom"}),
AddedIndex2 = Table.AddIndexColumn(RemoveColumns, "S NO.", 1, 1),
ReorColumns = Table.ReorderColumns(AddedIndex2,{"S NO.", "Custom"})
in
ReorColumns

Link below to file on my google drive
https://drive.google.com/file/d/0B6UlMk8OzUrxMU04MWl2NnAwZmc/view?usp=sharing

 

[Sunil M P]

Thanzz @billszysz.To be specific, our requirement was a bit different. We need the count for "Application Ended" in between "Application Started". For e.g. lets say if the sample data is as below:[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]ROW[/TD]
[TD]SESSION [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Application Started[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Application Aborted[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Application Ended[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Application Started[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Application Started[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Application Ended[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Application Started[/TD]
[/TR]
</tbody>[/TABLE]

the desired output should be:[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]S NO.[/TD]
[TD]Count of Application Ended between application starts[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[billszysz]

ok..try this code

let
Source = Excel.CurrentWorkbook(){[Name="tblStartEnd"]}[Content],
AddedCol = Table.AddColumn(Source, "Custom", each if [SESSION]="Application Started" then [ROW] else null),
FillUp1 = Table.FillUp(AddedCol,{"Custom"}),
FilterRows = Table.SelectRows(FillUp1, each ([SESSION] = "Application Ended") and ([Custom] <> null)),
GrRows = Table.Group(FilterRows, {"Custom"}, {{"EndedCount", each Table.RowCount(_), type number}}),
AddedIndex = Table.AddIndexColumn(GrRows, "Indeks", 1, 1),
RemCol = Table.RemoveColumns(AddedIndex,{"Custom"}),
ReorCol = Table.ReorderColumns(RemCol,{"Indeks", "EndedCount"}),
RenCol = Table.RenameColumns(ReorCol,{{"Indeks", "S NO."}})
in
RenCol

 

[Sunil M P]

Thanzzz billszysz,It worked perfectly..

 

[billszysz]

You are welcome