Overview: This post discusses situations when you may *not* want to load Power Query data to the worksheet, but instead to the data model only. One reason relates to file size limits in Power BI for Office 365; the other reason is to simplifying & de-duplicate the fields displayed in the Pivot Table Field List.

Power Query Settings

When you bring data into Excel via the Power Query add-in, you have some options on the Query Settings pane.

Enable download: Needs to be set to On in order to retrieve or download the data from the source.

Load to worksheet: Specifies whether the data downloaded will be stored in an Excel table within the worksheet. Default = Yes.

Load to data model: Specifies whether the data downloaded will be stored in the data model (i.e., Power Pivot). Default = No.

There’s a couple of reasons why you might want to flip-flop the above settings. Specifically:

Load to worksheet: Change to No.

Load to data model: Set to Yes.

Following are a couple of reasons why you may want to consider loading to the data model only.

File Size Limits for Browser Display in Power BI for Office 365

The default file size limit in Office 365 for browser display is 10MB. That limit for browser display has been increased to 250MB if all 3 of the following are true:

The workbook is enabled for Power BI (which requires SharePoint Online Enterprise E3 or E4 plan), and
The workbook contains a data model (i.e., an embedded PowerPivot model), and
Data in the workbook outside of the data model doesn’t exceed 10MB. <--includes Power Query data residing in an Excel table

This means you want to be very careful when working with Power Query to not necessarily import its data to the workbook, particularly if the volume of data is high. The two-step process to import only to the data model would be:

Move the slider “Load to worksheet” to Off. <--Important to click this one 1st before it becomes greyed out when you click #2.
Click the “Load to data model” option.

The reason this distinction makes a big difference in the file size is because the data model uses xVelocity compression. This is a much higher compression level than standard xlsx data.

Duplicates in the Pivot Table Field List

Loading the data to only the data model and not the accompanying workbook has another benefit. You won’t see the set of data twice in the pivot table field list.

It’s much cleaner having one clean set of fields to choose from. You can still use the “Filter & Shape” functionality of Power Query without loading the data to the worksheet.

Also, be aware that there’s some situations where Power Pivot will implicitly convert data to the data model, such as:

When Power Map is used within an Excel workbook. Power Map has a dependency on the data model for its functionality, so if you have not explicitly created a Power Pivot data model, it will do so on the fly when you insert a Power Map worksheet.
When you import two or more tables at once.
When you insert a Power View worksheet using a Table range of cells.

Finding More Information

Office – File Size Limits for SharePoint Online