Power Query

[Sunil M P]

Hi, below is my excel sheet , can we write a power query to find number of session( start to End)
Expected result for below excel is 2 , if suppose A12 had End Session the count to be 3.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start session [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]End Session
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Start session[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]End Session[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Start session[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I do have a huge data in working excel (taking lot of time using vba script ). please help to find a solution.
Thanks in advance,
Sunil

 

[RoryA]

Do you really need power Query for this? A simple countif formula would do that.

 

[Sunil M P]

Hi rory, I need the row difference count for each session, please help

 

[RoryA]

Perhaps you could show exactly what output you are after and where you want it?

 

[Sunil M P]

Hi rory, thanks for the quick response, There is a external excel sheet which I'm importing using power query. External excel looks similar to above table. I'm expecting the result to be loaded to active worksheet (Load to work sheet checkbox is checked). Expected result should look like

Session Size
1 3
2 2
3 1

 

[RoryA]

Is there always an End Session line for each Start Session?

 

[Sunil M P]

some times it will be session aborted, but lets assume there will End Session for every start session

 

[RoryA]

In that case, you can do this:
1. Add an Index column (called Index)
2. Filter the data so only the Start session and End Session rows are present
3. Add another index column (called Index.1)
4. Add a custom column called Counter with the formula:
=if Number.IsOdd([Index.1]) then ([Index.1]-1)/2+1 else [Index.1]/2+1
5. Remove the Index.1 column
6. Pivot the data on the original column (with Start Session and End Session in it)
7. Create a new custom column using formula:
=[End Session]-[Start session]-1
8. Hide the Start session and End Session columns.

There may well be simpler methods, but that should work.

 

[Sunil M P]

hi Rory,
I'm new to power query. I didnt get your 6th point, can you please tell how to pivot data.

 

[Sunil M P]

I got it, thanks Rory, its working fine