(table1 as table, fault_status as number, Index as number, ID as text)=>
let
row_count = Table.RowCount(table1),
current_row = Table.SelectRows(table1, each
[ID] = ID),
Index = current_row[Index],
index = List.Max(Index),
count = 0,
condition=true,
results = List.Generate(
()=>[index=index, count = 0],
each index > 0 and condition = true,
each[index = index -1, count = if table1{index+1}[fault_status] = 1 then count +1 else count +0, condition = if table1{index+1}[fault_status] = 1 then true else false],
each count
)
in
List.max(results)
First, my data is in the form of a column of 1s and 0s, I am trying to create a new column that contains how many 1s in a row there are in the prev column. e.g for the input of 0,0,0,1,1,0,1,1,1 - the output should be 0, 0, 0, 1, 2, 0, 1, 2, 3.
However, when I run this code (without the max function at the end) it creates a list for each item that is 1000+ items long, and all the values are 0, and when I run the code with the max function it takes a stupidly long time to execute, even with only 1 item in the input list.
I am very new to power query and especially lists, I am assuming I am fundamentally misunderstanding how list.generate works so any explanation or help would be greatly appreciated. Thanks.
