Power Query - Slow data load after filtering

Evans2

Board Regular
Joined
Jun 11, 2015
Messages
56
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"
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0

Forum statistics

Threads
1,226,694
Messages
6,192,473
Members
453,726
Latest member
JoeH57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top