cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sharpedogs Helper II
Helper II

Need to reduce refresh times...

Hi, 

I get a 8 files on the hour every hour each day. The files are dropped into a folder (not sharepoint or onedrive). I was pulling from the folder and appending the all files. But that file append now takes almost 45 minutes. 

 

I don't have a premium service and can't use incremental refresh because the source locaiton is file on a server. 

 

Here is what i am thinking of doing?

 

1) Creare two queres. The first I will have that captures and appends all data before 2nd of April. I will then turn off auto refresh and load the data. The secodn quere will pivk up files after 2nd the April. I'll use a date table to and a few DAX to calcualte totals. The idea here is that when the schedueld refresh takes place each hour it's only refreshing the 2nd quere that's all the data after April 2nd. Each week i'll update the dates so that I'm only refreshing a weeks worth of data.

 

Any suggestions on how i can make this refresh faster, the issue really is the number of files accumaltes so quickly, almost 200 a day that it takes time to refresh them all. 

 

If anyone knows of an yother tricks please let me know. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Need to reduce refresh times...

Unfortunately, unless your data source supports Incremental Refresh, and CSV files do not, no matter where they are stored, Power Query will process every single record in every single file every single refresh for all queries that get data loaded to the data model. your only hope of optimization is:

  1. make your queries more efficient. For example, if you are grouping or merging, grouping before merging can help as there is less to merge.
  2. Move your data into a server DB, like SQL Server via a script. You get two advantages here:
    1. Query folding means the server will do a lot of the work for you - perhaps all if 100% of your steps fold
    2. Incremental Refresh becomes possible.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
jeffshieldsdev Continued Contributor
Continued Contributor

Re: Need to reduce refresh times...

You can't turn off refresh on a single query.

I would create a dataflow to query my historical data (no need to refresh after inital pull), and then join with my live data query in the report in the Query Editor, not using DAX.

If all you need is an aggregation of the combination, I would look at doing that in dataflows too.

sharpedogs Helper II
Helper II

Re: Need to reduce refresh times...

Can you explain what you mean by you can't turn off refresh on a signle query?

 

If I right click on any query there is the option to "include in report refresh". Are you suggesting that even if i disable this option the report will still refresh this query?  

Highlighted
jeffshieldsdev Continued Contributor
Continued Contributor

Re: Need to reduce refresh times...

Correct.  "Include in report refresh" just applies to the Refresh button in Power BI Desktop, not the published report in Power BI Service.

watkinnc Helper I
Helper I

Re: Need to reduce refresh times...

If they are .csv files, you should make a cod or shell script to combine all the files into one file each hour, and then write your query against that combined file.
Super User II
Super User II

Re: Need to reduce refresh times...

Unfortunately, unless your data source supports Incremental Refresh, and CSV files do not, no matter where they are stored, Power Query will process every single record in every single file every single refresh for all queries that get data loaded to the data model. your only hope of optimization is:

  1. make your queries more efficient. For example, if you are grouping or merging, grouping before merging can help as there is less to merge.
  2. Move your data into a server DB, like SQL Server via a script. You get two advantages here:
    1. Query folding means the server will do a lot of the work for you - perhaps all if 100% of your steps fold
    2. Incremental Refresh becomes possible.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors