The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
19 | |
18 | |
16 | |
13 |