Flag consecutive rows with same values

[saachitalwar]

I wish to mark the first occurrence of a value as 1 and every consecutive occurrence as 0 so I remove the values containing 0 flag. I tried using the multi row formula tool but that didn't work.

if [row-1:Gross AUM] = [Gross AUM] else iif([row-1:Gross AUM]),1,0) endif

I have data in the form:

h1
123
123
123
123
123
123
456
456
456
456
456
456
456
456
789
789
789
789
789

and I wish to get it in the form:

h1
123
123
123
123
123
123
456
456
456
456
456
456
456
456
789
789
789
789
789

[binuacs]

@saachitalwar what is the expected output? 

[saachitalwar]

h1	count
123	1
123	0
123	0
123	0
123	0
123	0
456	1
456	0
456	0
456	0
456	0
456	0
456	0
456	0
789	1
789	0
789	0
789	0
789	0

correction in the required output

[DataNath · Accepted solution]

Hey @saachitalwar, if you're just wanting to mark the first occurrence and remove subsequent rows then you can group by the value itself and create an index within this partition. Then just filter for [Flag] = 1, like so:

However, if you have values that may repeat in future i.e. another block of 123, and you again want to capture the first occurrence, you'll need to do something close to what you were trying originally where you just mark a change in value with a flag and filter on that, like so:

Workflow attached for you to look through. Hope this helps!

[saachitalwar]

this worked. thanks