Need to reduce refresh times...

[sharpedogs]

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. 

[edhans · Accepted solution]

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.

[jeffshieldsdev]

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]

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?  

[jeffshieldsdev]

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

[watkinnc]

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.