Limit query refresh when working with Folder data source that are large?

Hi PBIUWO ,

 

There's two stages of data transfer that affect the speed of your refresh:

 

1) acquiring the data from source - this is what will be affected by network speed (e.g. VPN) unless you are using local files, and can be improved to some degree by reducing the data at source.

 

2) passing the data from Power Query to the data model - this will be affected by your workstation CPU and RAM, and can be improved as follows:

 

In order to limit the amount of data that gets applied to the data model for development, you can use a query toggle to change which step each query represents before being applied.

 

- Start by creating a new blank query called 'dbIsFiltered' or similar. Just apply a value of 1 to this query.

- Then, on each of your existing queries, apply the filter or limit to the data that you want to use when in 'development mode'. This may be filtering the table to only include data from yesterday, or applying the 'Keep Top N Rows' function. Do this as the LAST step on each of your queries that you want to reduce in size.

- Then, either add a custom step using the formula bar or edit the M code directly to add a new LAST step that basically reads "if dbIsFiltered = 1, then use the filtered step, otherwise use the unfiltered step".

Here's roughly how that would look in your code:

let
  Source = youDataSource,
  yourSteps = yourTranformationSteps,
  ...
  ...
  originalLastStep = The last, unfiltered step of your original query,
  filterStep = Data reduction step(s) e.g. date filters or TOP N rows,
  dbToggle =
    if dbIsFiltered = 1 then filterStep else originalLastStep
in
  dbToggle

 

What this allows you to do is control the output of all of your queries just by changing the value held in your dbIsFiltered query i.e. when the value is 1, all of your queries use a filtered output step, otherwise they use the original, unfiltered output step.

 

Hope this makes sense.

 

Pete

Hi PBIUWO 

Large size of data source ,complex power query, your network (if your data source is not in local computer) will impact the performance of power bi.

Here are some blogs to optmize your power bi performance.

Blog1: power query refresh really slow- excel file

Blog2: Optimization guide for Power BI 

Blog3:Power BI Performance Optimization Tips 

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.