Forum Discussion
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 "pat...
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
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
Brilliant!
Thank you!
