Overview: A few thoughts about using Data Explorer with and without the Excel Data Model (i.e., PowerPivot).

My first reaction when I heard about Data Explorer being released in preview mode as an add-in to Excel was “Why isn’t it part of PowerPivot?” Now that I’ve thought about it further, it’s occurred to me that its independence means you can use Data Explorer with or without PowerPivot.

Before we illustrate that further, let’s pull a bit of data into Data Explorer. One nice piece of functionality is that when you hover on the items in the Online Search pane on the right, the left side populates with a preview of the data immediately – this is nice to give you an idea if the data will be useful before you bother downloading it. Using the Online Search button on the Data Explorer ribbon, I’ve done a search for “Charlotte North Carolina” (just for grins) which is shown as follows:

Note that with this initial preview of Data Explorer, the only website being searched is Wikipedia. Options here will be enhanced over time. There’s numerous other ways to get external data as well (some of which are just really super cool, but that’s for another time…).

Using Data Explorer to Import Data to an Excel Table

The settings we are concerned with are in the Query Settings pane, shown as follows:

The default option, as shown above, is “Load to worksheet.” This is apparent because its slider is set to On. Also, by observing the Table Tools > Design ribbon we can see that the data downloaded went to an Excel table named Table2.

Note that this isn’t a Pivot Table – it’s an Excel Table applied to a range of cells.

Using Data Explorer to Import Data to a PowerPivot Model

Conversely, if you wish to import your data into the Excel Data Model (i.e., PowerPivot), then you want to select the “Load to data model” option on the Query Settings pane. After the data has been uploaded to the PowerPivot model, it will change your Query Settings pane as follows:

If you don’t see the Query Settings Pane, just click anywhere in the table & the pane should pop up. If it doesn’t, check the Table Tools > Query ribbon & select the “Show Query Settings” button.

Tip: if you need to modify the data and/or clean it up, you might want to handle this before you load it into the data model.

Just What is the Data Model Anyway?

If you are familiar with PowerPivot, then you are already familiar with the data model. I’d imagine the Excel team is going for ease of use & understandability with the data model terminology.

You can import data into the data model by using the PowerPivot add-in. Alternatively, a range of data can be added to the data model. The following screen shot shows a small range of cells I have highlighted & then selected “Format as Table” from the Home ribbon:

Next you are presented with a dialog box which confirms whether or not the first row is a header:

Now we have some options on the Table Tools > Design ribbon. If you choose “Summarize with PivotTable” you can interact with this range of data within the PivotTable field list.

Creating a PivotTable from a range of data is very cool, but not the reason I’m mentioning all this…note at the bottom of the dialog box there’s an option for you to “Add this data to the Data Model.” If you choose to add the range of data to the data model, you can then launch your PowerPivot window and see it there represented as a table.

This opens up a lot of possibilities considering you can bring data from a range of cells like this, and/or you can get external data from PowerPivot, and/or you can get external data from Data Explorer –> and then choose whether or not to integrate it with PowerPivot. That really is a lot of flexibility.

Deciding Where to Store the Data

The way I see it, you could keep the data in an Excel table if a tabular result set (i.e., the output from Data Explorer) is really all you need. You can even use a PivotTable with that Table range of data, which is very cool.

Here’s some reasons you might want to load the data obtained via Data Explorer into the data model (i.e., PowerPivot):

You have an existing PowerPivot data model, or other data sources, you wish to integrate this data with (although there is Merge functionality in Data Explorer that could work too).
You want to utilize other PowerPivot functionality such as Key Performance Indicators or Hierarchies.
You want to use the map feature of Power View (requires the geographic data to have a certain data category).
You want to change the default aggregation type from Sum to something else like Avg or Count.
You are more comfortable creating DAX calculations in PowerPivot than using the Data Explorer Formula Language (called “M”).
You wish to compress the data significantly.
You have a large set of data which will perform better using the xVelocity engine.

There’s times that Excel will implicitly create a data model for you, such as:

If you’re importing 2 or more tables at once
If you’ve inserted a Power View worksheet using the Table range of cells
If you've inserted a Power Map (its functionality requires a data model)

The implicit creation of the data model had me a bit stumped at first, but Excel is just being helpful. Folks without the PowerPivot add-in enabled won't even know when the implicit conversion to the data model happens.

Happy hunting for data!

Finding More Information

Office – Create a Data Model in Excel

Office – Microsoft “Data Explorer” Preview for Excel Help