Power Query - Slow data load after filtering

[Evans2]

Hi all,

Having a really tough time with Power Query. Does anyone know why I would be experiencing extremely slow filtering? I've connected to an oracle database with 7,000+ tables.

I'm only concerned with one of them but when I try to apply a simple filter to a column, It's taking 20 minutes to an hour. The more filters I apply, the slower it gets. Also, loading to the workbook takes a crazy long time.

Is this normal? I don't see anyone else complaining about this so I'm sure I'm doing something wrong. Is PQ trying to filter through all those 7,000+ tables?

let
Source = Oracle.Database("ORCL"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Database.Customer_Table")),
Database.Customer_Table = #"Filtered Rows"{[Schema="Database",Item="Customer_Table"]}[Data],
#"Filtered Rows1" = Table.SelectRows(Database.Customer_Table, each not Text.Contains([SO_NUMBER], "S-54398") or not Text.Contains([SO_NUMBER], "S-50208")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each not Text.Contains([SO_NUMBER], "S-50204") or not Text.Contains([SO_NUMBER], "S-50205"))
in
#"Filtered Rows2"

Thanks for any advance. This is driving me nuts.

 

[ImkeF]

The second step looks a bit strange. Try selecting the table directly (can't you just skip this step?).

You can also combine your filters on SO_NUMBER in one row:

#"Filtered Rows1" = Table.SelectRows(Database.Customer_Table, each not Text.Contains([SO_NUMBER], "S-54398") or not Text.Contains([SO_NUMBER], "S-50208") or not Text.Contains([SO_NUMBER], "S-50204") or not Text.Contains([SO_NUMBER], "S-50205")),

but I doubt that this makes the difference.

 

[Evans2]

Thanks Imkef,

I thought that the second step looked strange myself and have tried to just skip it by selecting the table directly but I'm not sure how to do that.

 

[Evans2]

I connected the oracle table I'm working with to Access in an attempt to clean up the data there before I import to PQ. I'm running into the same problem however. I can remove as much data as I want in Access and the query will load fine but, the slows way down once loaded into Power Query.

PQ will load the query from Access without a problem if there are one or maybe two filters but will load extremely slowly or not at all if I apply more. SQL from Access below. I don't understand why Power Query would bog down like this.


SELECT GUIDE_CUSTOMER_VIEW.CUST_NAME, GUIDE_CUSTOMER_VIEW.CUSTOMER_FULL_NAME, GUIDE_CUSTOMER_VIEW.ORIGIN, GUIDE_CUSTOMER_VIEW.PRODUCT, GUIDE_CUSTOMER_VIEW.SALES_PRODUCT_SPEC, GUIDE_CUSTOMER_VIEW.SALES_CONTRACT_STATUS, GUIDE_CUSTOMER_VIEW.INVOICED_QTY, GUIDE_CUSTOMER_VIEW.PO_NUMBER, GUIDE_CUSTOMER_VIEW.SO_NUMBER, GUIDE_CUSTOMER_VIEW.SO_INVOICED_QTY, GUIDE_CUSTOMER_VIEW.INV_DATE, GUIDE_CUSTOMER_VIEW.INVOICE_NO, GUIDE_CUSTOMER_VIEW.INV_PRICE, GUIDE_CUSTOMER_VIEW.INV_AMOUNT, GUIDE_CUSTOMER_VIEW.SO_INCO_LOCATION, GUIDE_CUSTOMER_VIEW.WH_REF

FROM GUIDE_CUSTOMER_VIEW WHERE CUST_NAME <> “TOM” AND CUST_NAME <> “TOM-B” AND SO_NUMBER <> “S-59864” AND SO_NUMBER <> “S-50902”;

 

[ImkeF]

Sorry, I have no idea of this performance behaviour as well.

To remove the second line - you can just pass // in front - this makes it like a comment:

let
Source = Oracle.Database("ORCL"),
//#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Database.Customer_Table")),
Database.Customer_Table = Source{[Schema="Database",Item="Customer_Table"]}[Data],
#"Filtered Rows1" = Table.SelectRows(Database.Customer_Table, each not Text.Contains([SO_NUMBER], "S-54398") or not Text.Contains([SO_NUMBER], "S-50208")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each not Text.Contains([SO_NUMBER], "S-50204") or not Text.Contains([SO_NUMBER], "S-50205"))
in
#"Filtered Rows2"

You then need to reference the following step to its new predecessor - which will be the "Source"-step.