How to trigger an sql routine on query column values and merge result into powerquery

Question

I am working with office 2016 excel and connecting to an Oracle db

I am creating a file for fetching orders, the part number, their desired delivery dates, the actual delivery dates, the average lead time, the average consumption and finally the current stock level.

I have created an sql to fetch the orders and the dates and the lead time. However, in the database, the current stock level is gotten through a procedure that takes input the part number and the location (can be '%' for overall stock level). Now, I can build a query that takes all orders, all part numbers and run the procedure for every single part number, and then build my powerquery but that seems like a hideous waste of processing power.

Question is: Is there a way to append or merge into the powerquery by calling the procedure after I have filtered the initial source results? Thus only running the procedure for orders made the last month or only on filtered part numbers?

I have tried looking the usual places, support.office, google and here but my problem is I (overwhelmingly) get only results from how to append or merge queries (which is trivial and basically a version of the unwanted situation)

Answer 1

Stian,

1. If your procedure is not a stored procedure, but a function, then you can create separate query for this function, and then add new column to a filtered table. This column is getting its value by executing this function with a parameter from another column(s).
2. The other way doing this is creating a view having all the columns you'd like, including that one generated by procedure. Then you query this view in PowerQuery, and apply your filters to the result. This should trigger query folding, which passes your filters to the server, so it optimizes query and doesn't fetch unneeded rows. As far as I know, Native Queries (when you directly white an SQL query to execute) are not subject to query folding. Also, they are quite unsecure and generally an ad-hoc solution. Keep this in mind.

Google for "Query folding in Power Query" if you'd like more info.

Still, for the most optimal scenario I would consider using function instead of procedure (if that's possible, of course), and combining if with a view to get the data you want.