Conditional Incremental in sorted data in Power Query

Question

My dataset

The dataset is in Excel. I have a column with unique IDs, some data that is out of scope of this question, and an "if" condition which returns true or false based those data. The first true/false is Null because it evaluates the data from the previous row to determine the boolean.

ID     boolean
15032  null
25638  false
45035  false
55139  true
15235  true
14550  false
15035  false
13035  false
12235  true 

Desired Output

Add a column called ConditionalIncremental. Starting at 1, incrementing only when the row's boolean is equal to TRUE, otherwise it stays the same as the previous row.

ID     boolean   ConditionalIncremental
15032  null      1
25638  false     1
45035  false     1
55139  true      2
15235  true      3
14550  false     3
15035  false     3
13035  false     3
12235  true      4

Basically; if boolean = true then previous row + 1 else previous row.

What I've found

• All references I find are about assigning indexes based on the group ID common to each record. My issue is that I want to CREATE the group ID (it is the incremental conditional index). such as this https://www.myonlinetraininghub.com/numbering-grouped-data-power-query

Answer 1

Make sure the boolean column is set to type logical

Add column .. index column ...

Add column ... custom column ...

= 1+List.Sum(List.Transform(List.FirstN(#"Added Index"[boolean],1+[Index]), each if _=true then 1 else 0))

sample:

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"boolean", type logical}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Incremental", each 1+List.Sum(List.Transform(List.FirstN(#"Added Index"[boolean],1+[Index]), each if _=true then 1 else 0))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in  #"Removed Columns"