Refresh fails UNLESS first previewing each table in Transform Data

[Anonymous]

I have a model that I refresh weekly when I receive new CSV sources files from the originating systems.  These refreshes almost always fail -- unless I first go into Transform Data and preview each table in PowerQuery.  This always solves the issue and no modifications to the PowerQuery queries are necessary - so the data structures haven't changed and the PowerQuery queries do effectively transform the source data for loading in the model.  While in the end, I am able to get the model refreshed but it is slow and inefficient to have to preview each PowerQuery table before I can do a refresh.

Are there any suggestions on where to look to see what is causing this or where I may need to tune or change settings to help with this and let me just do the refresh directly?

Thanks,

[Anonymous]

Hi,

a) I am not doing the refresh on PBI Service.  Instead I am doing the refresh in PBI Desktop.  I only use PBI Service to publish the dataset after refresh and let the users consume the visuals from there.

b) I am using the Oct 2022 release - ver 2.110.1161.0 64 bit

c) The issue still exist.  When I refresh WITHOUT going into Tranform Data and previewing each table / query, I receive the following error on one of the main transformed tables 'QUERY / TABLE NAME (step 'xxxx') referneces other queries or steps, so it may not directly access a data source.  Please rebuild this data combination."  However, if I first go into Transform Data, preview each table / query, and then Close or Close and Apply, and then refresh I do not receive the error and all works fine.  Again, nothing has changed since the last refresh other than I have new, updated CSV data source files (no change in columns or column names, just updated data).

Note: The transformation queries I have are extensive with many steps as I shape the source data for loading into the model, so I recognize the refresh is extensive and a heavy load.  However, I can not determine why I need to do the preview steps so that PBI "remembers" how to do this from the previous time I refreshed.

Note: from the earlier solution, I did increase the Maximum Allowed (MB) setting under Data Cache Management Options from 4096 to 8192.  This did not solve the problem as it still occurs as noted above.

Any thoughts on where to look next to identify the problem or potential solutions?

Regards,

[v-easonf-msft]

Hi, @Anonymous 

It's hard to reproduce it.

If the problem persists, it is recommended to open a support ticket to let MS engineers look into the issue on your side.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Note: I did find a post them seems to be related.  Solved: Data Refresh in Desktop fails w/o opening data in ... - Microsoft Power BI Community

Are there any other recommendations or suggestions beyond those outlined in that post?

[v-easonf-msft]

Hi, @Anonymous 

Are you using the latest version of PowerBI?
After uploading the report to the service and configuring the scheduled refresh, does the error still appear?

Best Regards,
Community Support Team _ Eason