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

[Jake Blackmore]

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

 

[ImkeF]

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

 

[ImkeF]

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