Index by pattern

[Ara_Karapetyan]

Hello,

My data have a pattern, generally of 4 rows, but sometimes it breaks by 3 or 5 rows.

I am looking for solution to start again the "pattern_index" from zero every time I have TRUE in the "pattern" column.

As now I use Number.Mod([index], 4) in the "patten_index".

Thanks in advance

index	pattern	pattern_index
0	TRUE	0
1	FALSE	1
2	FALSE	2
3	FALSE	3
4	TRUE	0
5	FALSE	1
6	FALSE	2
7	FALSE	3
8	TRUE	0
9	FALSE	1
10	FALSE	2
11	FALSE	3
12	TRUE	0
13	FALSE	1
14	FALSE	2
15	FALSE	3
16	TRUE	0
17	FALSE	1
18	FALSE	2
19	FALSE	3
20	TRUE	0
21	FALSE	1
22	FALSE	2
23	TRUE	3
24	FALSE	0
25	FALSE	1
26	FALSE	2
27	TRUE	3
28	FALSE	0
29	FALSE	1

[dufoq3 · Accepted solution]

Hi @Ara_Karapetyan,,

Fastest solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZArDoAwEAXvshrRfS20SAQoFB/V9P7XgATTQU52J5m8Wi3YYNdxr9aGav7CtuznRwJFUOq1EacJlEGl12acPBDZ4kInYzwR2eMTVAZ5IbJJWEe/ebiPIn6ZJCaJGylDZZK6pPYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [index = _t, pattern = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"pattern", type logical}, {"index", Int64.Type}}),
    IndexPattern = 
        [ p = List.Buffer(ChangedType[pattern]),
          lg = List.Generate( 
            ()=> [ x = 0, y = 0 ],
            each [x] <= List.Count(p) -1,
            each [ x = [x]+1, y = if p{x} = true then 0 else [y]+1 ],
            each [y] )
        ][lg],
    Ad_IndexPattern = Table.FromColumns(Table.ToColumns(ChangedType) & {IndexPattern}, Table.ColumnNames(ChangedType) & {"Index Pattern"})
in
    Ad_IndexPattern

[Ara_Karapetyan]

Brilliant!

Thank you!