Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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!