Forum Discussion

luigiPulido_'s avatar
luigiPulido_
New Member
2 years ago

Performance issue in List.Contains Step - 30 minutes for 500 records

In a previous post I asked how to get all the records where orders includes an specific product.

Example:

Expected result if I'm looking for orders that include 'bbbb'

Thanks to AlienSx  for the answer.

 

But then I tried to filter all the table to get records where orders don't have the product 'bbbb'. This is the expected result

 

and I achive this with the following code:

 

 

 

let
    Source = table,
    ordersToExclude = Table.SelectRows(Origen, (x) => x[Product] = "bbbbb")[Order],
    allOrders = Table.SelectRows(Origen, (x) => Text.Start(x[Order],1)="#" )[Order],
    ordersIwant = List.RemoveItems(allOrders,OrdersToExclude),
    removeDuplicates = List.Distinct(OrdersIwant),
    finalTable = Table.SelectRows(Source, (x) => List.Contains(removeDuplicates,x[Order]) )
in
    finalTable

 

 

 

The problem with that code is that it takes a lot of Time and resources for excecution. I run it with a file that only have 515 rows and it takes more than 30 minutes and almost 20GB of memory (not sure which memory). Insane

 

 

My thoughts is that there is like a big loop in the finalTable step. That's why I apply the remove duplicates step but it still takes a lot of time. 

 

With my real data of 515 rows, after remove duplicates there is only like 90 unique orders without the 'bbbb' product.

 

So my questions are:

 

  • My assumption with the loop is correct and thats why is taking so much time or what is happening??
  • Do you see another posible solution?

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Buffer those List.Contains, lad. Always buffer your List.Contains lists.

    each List.Contains((List.Buffer(removeDuplicates, [Order]) 


    You won't believe the difference. 

    --Nate

  • Hello, luigiPulido_ my 2c. While suggestion made by Anonymous should definitely boost performance, I would also try alternative way to filter orders using Table.Group. Group your data by [Order] and create 2 aggregated columns:

    1. all order items as {"all", each _}

    2. logical test if products list contains unwanted item as {"test", each List.Contains(_[Product], "bbbb")}

    Then filter test and expand all.