need some suggestion - fetch data from view in sql server hourly ?

Hi suvechha ,

 

I think the answer is query folding.

Connect to the view using the SQL Server source. This will only import the first 1,000 rows for preview.

Make your transformations, like filters, removing columns etc. and, for the most part, these will get folded back to the server to reduce the query volume.

You can check which steps have been folded to the server by right-clicking on the Power Query steps and seeing if the 'View Native Query' option is lit up. If it is, all the steps up to that one have been folded. If not, the folding has been broken by one of your steps and they should be reordered/adjusted to maintain folding for as much of the query as possible.

If you need all 3.5m rows and all columns, and you continue to get memory errors, there's not much you can do but directly address the memory issues via hardware etc.

 

Pete

suvechha  if you are on premium workspace create a dataflow where you write a native optimized sql query to return the data from sql view and have that scheduled to refresh every hour. What works for me is not using PQ for any of the transformation, rather doing it through native sql to do the heavy lifting.

Create a seperate pbi report where you query that dataflow to build a report. Publish that in the workspace and have that refreshed every hour with an increment of 30 min from the data flow. In this way you can run the transformation and reporting parallely and optimize.