Power Query - Slow data load after filtering

[Evans2]

Hi all,

I have power query connected to a table of about 30,000 rows in an oracle database. There are some rows in the table that I need to exclude from the data model in power query. I'm using the text and number filters to remove the rows I don't want. I have 6 filters removing unwanted rows which is working. However, the 7th filter I apply (Row 7 below) slows the data load significantly and I'm not sure why this is.

Is there another way I should be going about this? Is there something I can write in M in the query that will remove the unwanted rows without slowing down the data load so much?

Thanks very much for the help,

let
Source = Oracle.Database("),
Customer View= Source{[Schema="Customer View"]}[Data],
#"Removed Columns" = Table.RemoveColumns(Customer View,{"SO_DATE", "SO_YEAR", "SO_MONTH", "CUST_REF", "PROFIT_MARGIN_CURRENCY", "DELIVERY_FROM", "DELIVERY_TO", "SO_INCO_TERM", "SALES_REP", "SO_INCO_LOCATION", "CONT_QTY", "CONT_PACKING", "CONT_WT", "ALLOCATED_QTY", "ALLOC_QTY_PACKING", "ALLOCATED_WT", "UNALLOCATED_QTY", "UNALLOC_QTY_PACKING", "UNALLOCATED_WT", "INVOICED_QTY_PACKING", "INV_CURRENCY", "SO_INVOICED_QTY", "SO_INVOICED_WT", "SO_UNINVOICED_QTY", "SO_UNINVOICED_WT", "STORAGE_NO", "WH_REF", "ICO_NO", "SALES_CONTRACT_STATUS", "SALES_PRODUCT_SPEC", "SO_PRICE_BASIS", "SALES_PRICE_CURRENCY", "SALES_PRICE", "SALES_PRICE_UOM", "PO_PRODUCER_LONG_NAME", "PO_SHIPPER_LONG_NAME", "SO_POSITION", "SO_APPROVAL_BASIS", "DELI_NO", "DELIVERY_BY_VALUE", "DELIVERY_PAID_BY"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"INV_DATE", type date}, {"INVOICED_QTY", Int64.Type}}),
#"Multiplied Column" = Table.TransformColumns(#"Changed Type", {{"PROFIT_MARGIN", each List.Product({_, 100}), type number}}),
#"Reordered Columns" = Table.ReorderColumns(#"Multiplied Column",{"CUST_NAME", "CUSTOMER_FULL_NAME", "PROFIT_MARGIN", "ORIGIN", "PRODUCT", "PRODUCT_SHORT_NAME", "INVOICED_QTY", "PO_NUMBER", "SO_NUMBER", "INV_DATE", "INV_MONTH", "INV_YEAR", "INV_PRICE", "INV_AMOUNT", "INVOICE_STATUS", "INVOICE_NO", "INVOICED_WT"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"INVOICED_WT", "Invoice Wt"}, {"INVOICE_NO", "Invoice No"}, {"INVOICE_STATUS", "Invoice Status"}, {"INV_AMOUNT", "Inv Amount"}, {"INV_PRICE", "Inv Price"}, {"INV_YEAR", "Invoice Year"}, {"INV_MONTH", "Invoice Month"}, {"INV_DATE", "Inv Date"}, {"SO_NUMBER", "SO Number"}, {"PO_NUMBER", "PO Number"}, {"INVOICED_QTY", "invoice Qty"}, {"PRODUCT_SHORT_NAME", "Product Short"}, {"PRODUCT", "Product"}, {"ORIGIN", "Origin"}, {"PROFIT_MARGIN", "Margin"}, {"CUSTOMER_FULL_NAME", "Customer Name"}, {"CUST_NAME", "Short Name"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each not Text.Contains([PO Number], "P-7318-A")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([Customer Name], "Atlas")),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each not Text.Contains([SO Number], "S-50085")),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each not Text.Contains([SO Number], "S-50204")),
#"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each not Text.Contains([SO Number], "S-50205")),
#"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each not Text.Contains([SO Number], "S-50207")),
#"Filtered Rows6" = Table.SelectRows(#"Filtered Rows5", each [Invoice Year] <> 2013),
#"Filtered Rows7" = Table.SelectRows(#"Filtered Rows6", each not Text.Contains([SO Number], "S-50810"))
in
#"Filtered Rows7"

 

[ImkeF]

Do the filtering before the transformations (Multiplied Columns, Reordered Columns, Renamed Columns)
Move step Filtered Rows7 before Filtered Rows 6

Have a look at this blogpost

 

[Evans2]

Trying out those suggestions. Thanks again Imkef.