Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register 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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
14 | |
13 | |
12 | |
12 | |
12 |