Tuesday, September 22, 2015

Loading Power Query data to Power Pivot: Power Query Default Query Load Setting

This concern can be seen everywhere, why my Power Query data sometime appears in Power Pivot but sometime does not appear in Power Pivot? This is not a bug or an issue, it is all about Default Query Load Settings. When data is loaded using Power Query, the placement of the loaded data is decided by Power Query using Default Query Load Settings. This has mainly two settings;
  • Use standard load settings
  • Specify custom default load settings
    • Load to worksheet
    • Load to Data Model

By default, Use standard load settings is selected and its behavior gets changes based on the way you load data. It loads data to worksheet if only a single table is selected. It loads data to data model when multiple tables are selected or loaded data set is not fit enough to worksheet (row limitation). If the setting is changed to Specify custom default load settings, either one of the options (Load to worksheet or Load to Data Model) can be set or both can be selected.

This is how it works;

With the Use standard load settings selected, when a single table is selected from the data source;

and if it is fit enough for the sheet, it will be loaded to worksheet, not to model.

When a table is selected with Select multiple items checked;

data is loaded to the model.

The same behavior for other data sources too;

If Specify custom default load settings is set, and both Load to worksheet and Load to Data Model are checked;

data will be loaded to both worksheet and model.

In addition to that, placement can be decided with Load button in Power Query Loading windows too;

Note that this new configuration was opened with the latest update of Power Query. It can be downloaded from: http://www.microsoft.com/en-us/download/details.aspx?id=39379&WT.mc_id=blog_PBI_General_DI

And you can read more on this update at: http://blogs.msdn.com/b/powerbi/archive/2014/05/06/new-updates-for-power-query.aspx

No comments: