Forum Discussion

ptrick37's avatar
ptrick37
New Member
3 years ago

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_Daniel's avatar
    wdx223_Daniel
    Community 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

    • ptrick37's avatar
      ptrick37
      New 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.

    • ptrick37's avatar
      ptrick37
      New 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?