Forum Discussion
Enable load/Include in report refresh - Bug or Feature?
Our customer migrated to SAP, but couldn't load all historical data, so we kept it in the data wareshouse. Therefore for BI reports I did some merging and appending (of SAP and DWH) and I was hoping, that if I disable "Enable load" and "include in report refresh", I will be able to load some tables from the DWH, merge or append it, and then disable load and refresh. This way I would get all data (historical and current).
I was wrong.
To simulate what I found out, I have created two excel tables - List A and List B each in a separate document.
Imagine, that list B isold data from DWH. I don't need them to be refreshed (in reality it is about 120k rows). So to demonstrate what will be happening, aside from loading List B, I have created copies, where I disabled Load and disabled load and refresh (you may notice it in the table names.
I have also appended and merged List A with List B that was disabled for load and refresh.
Next I created a report. So far, so good.
But look, what happens, when I have the data in the excel sheet List B:
For some reason, if the query with disabled refresh is included in some other query then it refreshes anyway...?!!
Does anyone know some workaround? Thanks. I dont need 120k rows to refresh every day without any reason.
7 Replies
zenisekd , Once one disable - Enable load, I doubt the table will be even loading and available. Are still able to use that?
As presented, if you disable "Enable load", it won't load, but if it is included in some merged or appended query, the data are there and what is very wrong, is that it also refreshes. Test if yourself.
Now I am looking at ways how to get the data there so they would not refresh, even if used in append or merge.HI zenisekd,
I don't think you can use these 'data load' options to achieve historical data effects.
AFAIK, M query tables have displayed the snapshots that preview of M query function processing result. They haven't really been stored in the data models. (normally this operation will proceed when you apply and submit these changes to save back to the data view)
So if you turn off the 'enable loading' option, these table records will not load/process to generate corresponding data tables into power bi data models.
So it means you can use this to store some cold data but it is trouble to achieve historical and current data effects. You need to manually refresh on the power query side and publish the report to replace old version data. (Also, the query tables refresh cannot be processed at the same time or the query table which is used to store history data will also get the latest data)
Managing query refresh - Power BI | Microsoft Docs
In my opinion, I'd like to suggest you create multiple tables with different DB 'queries' to limit the data loading ranges to achieve your requirement. (this should not suitable for the database which will replace the history records values)
Regards,
Xiaoxin Sheng
Anonymous I am not sure I follow, could you please try to explain on the example I have posted?
Based on Managing query refresh - Power BI | Microsoft Docs one would say, that if I Exclude the query from the refresh, it would mean that B will stay static even in the appended and merged queries, but it doesnt.
Does it mean, that there is no way to append two queries, where A query would use scheduled refresh and B would be without refresh, static? BTW, query A is from a different database then query B.
What about scheduled refresh, could I use scheduled refresh at the appended query (A+B) to refresh data only from A?
I am really confused here. I just want to connect (append) old database (with some transformations) to new database and reduce the traffic, disable refresh of old database.
Similar problem here: https://powerusers.microsoft.com/t5/Power-Query/Append-2-Queries-turn-off-refresh-on-only-1-of-the-queries/td-p/1265980https://www.reddit.com/r/PowerBI/comments/s97tc7/refresh_only_current_year_data_set_on_appended/
HI zenisekd,
'Include in report refresh' option will disable the refresh request so that the target data model table will keep current data without refresh.
'Enable load to report' option will affect the backend and front data model table transform. If you turn off it, this target query table will keep the preview snapshot of query table steps and its M query code did not process to generate the corresponding data mode tables.In addition, scheduler refresh also works on the dataset instead of detailed data tables. If you want to accurately control the refresh of each table, you can take a look at the following blog to use TMSL(Tabular Model Scripting Language) to config the refresh.
Trigger a single table to refresh in the Power BI Service – Data – Marc (data-marc.com)
Regards,
Xiaoxin Sheng
