PQ - Count data in column excluding successive repetitions

[Papale]

First of all, sorry if the title is not explaining the problem properly. It was hard to come up with a proper title as I am fumbling in the dark here.

What I want to solve is this: I have a set of data in a 4-digit number of columns and 2-digit number of rows. In one of the columns information about different modes for offshore vessels are given. This is information the vessel changes when it changes modes. I want to count each time the vessel is changing mode. The data is displayed as follows (example):


Vessel A - 21.06.2018 12:10:55 - Transit - Column 4 - Column 5....
Vessel A - 21.06.2018 12:11:55 - Transit - ……………………………..
Vessel A - 21.06.2018 12:12:55 - Transit - ……………………………..
Vessel A - 21.06.2018 12:13:55 - In Port - ……………………………..

Vessel A - 21.06.2018 12:14:55 - In Port - ……………………………..

Vessel A - 21.06.2018 12:15:55 - In Port - ……………………………..

Vessel A - 21.06.2018 12:16:55 - Transit - ……………………………..

Essentially, what i want is to count each time a new instance of a mode appears (each time the vessel changes mode). In this case I want to count transits. Here is shown two seperate transits, as the successive rows are datasets from the same transit.

As I am not very advanced in the use of Excel and PQ, I was hoping some of you might know of a way to solve this. Perhaps there is a formula to count each time information in a column changes or something along those lines.
​

​

 

[Papale]

I am working in Power Query editor and displaying the information in Pivot Tables / Charts, for info.

 

[VBA Geek]

Hello, maybe you can post a better data set along with expected results?
With the above data it's difficult to attempt anything

 

[Papale]

VBA Geek: Thanks for reply, see image for better data set. (I'm not allowed to post attachements to the forum as I newly registered).

The wanted result is to be able to count all actual transits in the data set. As you see from the image, the mode "Transit" is listed several times, as the vessel registers data every minute. In a transit lasting e.g. an hour, there will be 60 successive rows labeled Transit, when this in fact is the same continuous transit. I want to be able to count this as one transit, then one "In Port"-mode for the 10 listings of this mode (as this is successive and therefor the same port stay), and then another transit for the 11 listings below.

I want to count 2 "Transit" and one "In Port" in the exemplified data set (image). Take into account that there can be 1000+ rows, and 6 different potential modes in a real scenario.

 

[Papale]

https://imgur.com/8IRZpYg

 

[VBA Geek]

I have recreated your image and turned it into a table named Vessels

Then the below M will generate the following result:

let
    Source = Excel.CurrentWorkbook(){[Name="[B]Vessels[/B]"]}[Content],


    Grp = Table.Group(Source,
                      {"Mode"},
                      {{"Count1", each 1, Int64.Type}},
                      GroupKind.Local),


    Results = Table.Group(Grp,
                          {"Mode"},
                          {{"Count", each List.Count([Mode]), Int64.Type}},
                          GroupKind.Global)


in
    Results