Part V: Understanding Power Pivot and Power Query
Activate the Power Pivot window, and click the From Other Sources button on the Home tab.
This will activate the same Table Import Wizard as shown previously in Figure 35.13.
Select the appropriate relational database system from the plethora of options. If you need
to import data from Oracle, select Oracle. If you need to import data from Sybase, select
Sybase.
Connecting to any of these relational systems takes you through roughly the same steps
you saw when importing SQL Server data earlier in this chapter. You may see some alternate
dialog boxes, however, based on the needs of the database system you select.
Understandably, Microsoft cannot possibly create a named connection option for every
database system out there. So, you may not find your database system listed. In this case,
simply select the option called Others (OLEDB/ODBC). Selecting this option opens the Table
Import Wizard starting with a screen asking you to enter the connection string for your
database system.
Loading data from flat files
The term flat file refers to a file that contains some form of tabular data without any sort
of structural hierarchy or relationship between records. The most common types of flat
files are Excel files and text files. A ton of important data is maintained in flat files. In this
section, you’ll discover how to import these flat file data sources into the Power Pivot data
model.
Loading data from external Excel files
Earlier in this chapter, you created linked tables by loading Power Pivot with the data con-
tained within the same workbook. Linked tables have a distinct advantage over other types
of imported data in that they immediately respond to changes in the source data within
the workbook. If you change the data in one of the tables in the workbook, the linked table
within the Power Pivot data model automatically changes. The real-time interactivity you
get with linked tables is really nice to have.
The drawback to linked tables is that the source data must be kept in the same workbook as
the Power Pivot data model. This isn’t always possible. You’ll encounter plenty of scenarios
where you’ll need to incorporate Excel data into your analysis but that data lives in another
workbook. In these cases, you can use Power Pivot’s Table Import Wizard to connect to
external Excel files.
Activate the Power Pivot window and click the From Other Sources button on the Home tab.
This will activate the Table Import Wizard dialog box. Scroll down and select the Excel File
option in Figure 35.18 and then click the Next button.
The Table Import Wizard will now ask for all the information it needs to connect to your
target workbook. In this screen, you’ll need to provide the following:
