Using and manipulating a variable in if statement in Power query

[BmBogers]

Hello, 

I wrote some code in the advanced editor, the goal of this code is to count the difference in days between the current record and the previous record in a certain group. I managed to do that by adding a column called 'Verschil in dagen'. So far, So good.

Now I have the difference in days in a column, I would like to group them within the current group and give them a unique ID.
The records are only allowed to be grouped if the difference in days between two records is smaller then 32, if it is not; a new group(read: id) should be created.

Since I am looping through the records already, I was thinking about creating a variable (Named 'TrajectCounter') that would simply count the amount of times the difference in days is smaller then 32. Here is where I get stuck.. 

TrajectCounter = 1,
#"Sorted Rows1" = Table.Buffer(Table.Sort(NewTable,{{"Factuur startdatum voorziening", Order.Descending}})),
#"Added Custom2" = Table.AddColumn(#"Sorted Rows1", "Traject_id", each   
                                   if [Verschil in dagen] < 32 or [Verschil in dagen] = null then 
                                      TrajectCounter 
                                   else 
                                      TrajectCounter + 1 )

The first part of the code runs fine and does what is has to do, when it reaches the else statement it's going wrong.
It should just + the 'TrajectCounter' variable by 1 and return it (and remember that is has been +'ed!).

I have tried something like this:

else 
   TrajectCounter = TrajectCounter + 1,
   TrajectCounter )

But that doesn't work.

Is there a way to plus the 'TrajectCounter' variable and return it in the same statement?

[MarcelBeug · Accepted solution]

In general, "looping" is not often required in Power Query.

My suggstion wold be to first add an Index column and next add a column: with the Index value if a new group must start, and null if it is the same group.

Next you can filldown the values in the column, so you hve all the same values within each group.

A bit short explanation as I have other priorities now; hopefully it helps and otherwise I can take another look later today.

[BmBogers]

Amazing, thanks!