Forum Discussion
List.Generate creates list with more rows than expected
(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.
3 Replies
- wdx223_DanielCommunity Champion
let
source=table1,
custom1=Table.FromRecords(List.Accumulate(Table.ToRecords(source),{{},[]},(x,y)=>let a=(Record.FieldOrDefault(x{1},y[ID],0)+y[falult_status])*y[fault_status] in {x{0}&{y&[NewCol=a]},x{1}&Record.AddField([],y[ID],a)}){0})
in
custom1
- ptrick37New Member
Thank you for your reply, could you be able to tell me what was wrong with my method above? To my understanding this should have worked.
- ptrick37New Member
Also when I run this solution it was taking more than 1 hour to run with 150 entries so I killed it. Is this normal for power bi?