Disabling Slicer triggered Data model refresh sourced by Power Query referencing an External Source

Jake Blackmore

Board Regular
Joined
Nov 24, 2014
Messages
200
Hello all,

Thank you ever so much for looking at my question.

I have a model that the user intends to distribute and therefore needs to stay small in size. To accomplish this I have taken a large csv file (137k rows in a flat table) and imported it via Power Query. I have then twisted it slightly to meet my needs and load loaded it to the Data Model. From here I'm able to use Power Pivot to slice the data up in a variety of ways on various tabs for different reports.

My problem is that when I first open the file and go to play with one of the pivot table slicers a full refresh seems to be triggered. I can see it loading the data from the CSV file and then into the data model and eventually the pivot cache seems to be loaded and the pivot table refreshes. After this there is no further refreshing and the slicers work as expected.

At the moment the file size is only 3mb and if I attempt to change the Power Query by using "Load To" sheet and then have a normal pivot table the file size goes up to 23mb which I then can't email. I had hoped this approach might offer a break point of the full refresh.

The file type is .xlsm and whilst saving to .xlsb could reduce file size I doubt my Power tools would be as pleased / efficient.

I've also tried deleting the connection on a saved copy of the file but then the model falls over "issue with data model please restart excel".

Is there a way to disable this full refresh or put some sort of step in place so the user can simply play with the file as it is without being able to connect to the original CSV?

Thank you again for your time,
Jake
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi Jake,
there is a Data Load option in Power query for the current workbook that allows you disable the download of preview data in the background. Try if disabling this helps.
Cheers, Imke :)
 
Upvote 0
Hi Jake,
this got me curious and I checked it out. Disabling it doesn't help.
But whatever happens there, doesn't seem to be a full load of the data. I checked it against a file that loads 30 Mio row from a SQL DB and I could replicate your observations. But fortunately the time it took (to load whatever it does) is only a small fraction of the time it takes to load all the data to the model.
So: Good observation here, but my guess is that this will not cause a load of considerable burden if applied to larger datasets.
If you want to investigate further what happens here, try to analyze the trace log like described here: https://blog.crossjoin.co.uk/2016/0...s-trace-logs-and-query-execution-times-again/
... and post your findings, as this would interest me at least :)
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top