Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi everyone,
Any chance you could help with another indexing question? Basically I need to create an index which changes when the row value changes from TRUE to False and then to TRUE ect...
Here is an example:
Index | Value | Expected outcome |
1 | TRUE | 1 |
2 | TRUE | 1 |
3 | TRUE | 1 |
4 | TRUE | 1 |
5 | TRUE | 1 |
6 | FALSE | 2 |
7 | FALSE | 2 |
8 | FALSE | 2 |
9 | TRUE | 3 |
10 | TRUE | 3 |
11 | TRUE | 3 |
12 | FALSE | 4 |
13 | FALSE | 4 |
14 | FALSE | 4 |
15 | FALSE | 4 |
16 | FALSE | 4 |
17 | TRUE | 5 |
Thanks in advance!
Proud to be a Super User!
Paul on Linkedin.
Solved! Go to Solution.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
1. If performance is not a consideration, then Group by approach
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoJCnVVitWJVjJC5hgjc0yQOabIHDMgx83RJxjCM0fhWaDwLJG1GRqg8FCcYWiEos/QGJVrgso1ReWiusfQHG5wLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Value", type logical}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Value"}, {{"Temp", each _, type table [Index=nullable number, Value=nullable logical]}}, GroupKind.Local),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Value"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "New Index", 1, 1, Int64.Type),
#"Expanded Temp" = Table.ExpandTableColumn(#"Added Index", "Temp", {"Index", "Value"}, {"Index", "Value"})
in
#"Expanded Temp"
2. If performance is a consideration, then List.Generate approach
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoJCnVVitWJVjJC5hgjc0yQOabIHDMgx83RJxjCM0fhWaDwLJG1GRqg8FCcYWiEos/QGJVrgso1ReWiusfQHG5wLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Value", type logical}}),
ListOfValue = List.Buffer(#"Changed Type"[Value]),
RowCount = List.Count(ListOfValue),
GenerateIndex = List.Generate(()=>[x=1,i=0], each [i]<RowCount, each [i=[i]+1, x=if ListOfValue{i}<>ListOfValue{[i]} then [x]+1 else [x]], each [x]),
Result = Table.FromColumns(Table.ToColumns(#"Changed Type") & {GenerateIndex},Table.ColumnNames(#"Changed Type")&{"New Index"})
in
Result
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
1. If performance is not a consideration, then Group by approach
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoJCnVVitWJVjJC5hgjc0yQOabIHDMgx83RJxjCM0fhWaDwLJG1GRqg8FCcYWiEos/QGJVrgso1ReWiusfQHG5wLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Value", type logical}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Value"}, {{"Temp", each _, type table [Index=nullable number, Value=nullable logical]}}, GroupKind.Local),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Value"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "New Index", 1, 1, Int64.Type),
#"Expanded Temp" = Table.ExpandTableColumn(#"Added Index", "Temp", {"Index", "Value"}, {"Index", "Value"})
in
#"Expanded Temp"
2. If performance is a consideration, then List.Generate approach
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoJCnVVitWJVjJC5hgjc0yQOabIHDMgx83RJxjCM0fhWaDwLJG1GRqg8FCcYWiEos/QGJVrgso1ReWiusfQHG5wLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Value", type logical}}),
ListOfValue = List.Buffer(#"Changed Type"[Value]),
RowCount = List.Count(ListOfValue),
GenerateIndex = List.Generate(()=>[x=1,i=0], each [i]<RowCount, each [i=[i]+1, x=if ListOfValue{i}<>ListOfValue{[i]} then [x]+1 else [x]], each [x]),
Result = Table.FromColumns(Table.ToColumns(#"Changed Type") & {GenerateIndex},Table.ColumnNames(#"Changed Type")&{"New Index"})
in
Result
Superb. Thanks @Vijay_A_Verma !
Proud to be a Super User!
Paul on Linkedin.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
23 | |
18 | |
12 |