Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Hello, @luigiPulido_ my 2c. While suggestion made by @watkinnc 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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.