Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUnfortunately, 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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou 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.
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?
Correct. "Include in report refresh" just applies to the Refresh button in Power BI Desktop, not the published report in Power BI Service.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |