Forum Discussion

Ara_Karapetyan's avatar
Ara_Karapetyan
Helper I
2 years ago
Solved

Index by pattern

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

 

indexpatternpattern_index
0TRUE0
1FALSE1
2FALSE2
3FALSE3
4TRUE0
5FALSE1
6FALSE2
7FALSE3
8TRUE0
9FALSE1
10FALSE2
11FALSE3
12TRUE0
13FALSE1
14FALSE2
15FALSE3
16TRUE0
17FALSE1
18FALSE2
19FALSE3
20TRUE0
21FALSE1
22FALSE2
23TRUE3
24FALSE0
25FALSE1
26FALSE2
27TRUE3
28FALSE0
29FALSE1
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

2 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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