Indexing by change of row value

[PaulDBrown]

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!

[Vijay_A_Verma · Accepted 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

[PaulDBrown]

Superb. Thanks @Vijay_A_Verma !