- Downloading and Installing PowerPivot
- Getting your Data into PowerPivot
- Linking an Excel Table to PowerPivot
- Importing Data into PowerPivot
- Adding DAX Calculations to the Grid
- Creating Pivot Tables from PowerPivot
- Defining a Relationship Between Worksheets
- Using Automatic Relationship Detection
- Using Explicit Relationships
- Using the RELATED Function in the PowerPivot Grid
- Adding Slicers to Drive All Pivot Elements
- Adding DAX Calculations to the PivotTable
- Entering a Measure
- Using the X Functions
- Using CountRows and Distinct
- PowerPivot Is a Home Run
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Importing Data into PowerPivot
The Get External Data group in the PowerPivot window offers many sources from which you can import data. If you haven't opened the PowerPivot window yet, go to the PowerPivot ribbon tab in Excel 2010. Choose PowerPivot Window from the left side of the ribbon.
The From Database drop-down offers to import data from Access or SQL Server. Other icons offer Atom Feeds, Text Files, Sharepoint reports, and the Azure Data Marketplace. The From Other Sources icon offers an impressive list of supported data sources, as shown in the following figure.
)
The process will be slightly different for each data source. The following figures show a simple CSV text file.
- Choose From Text in the PowerPivot window.
- Choose the checkbox for Use First Row As Column Headers. Doing this first will save you several seconds later.
- Click the Browse button. Locate your text file.
- Excel will show you a data preview of the first 50 rows of the data set. Look through the columns. If there are any columns that you will never need to analyze, uncheck those columns.
- Click Finish.
Excel will import all of the data into the workbook. Even though PowerPivot uses a vertical compression scheme that often reduces the file size by a factor 15, you don't want to import 5 million rows of columns that you will never need. Long text values are great candidates for excluding. You can also filter which records are imported by using the Filter drop-downs at the top of each column.
)
Importing the data into memory could take a long time. A progress indicator will show how many rows have been imported. Eventually, Excel should report that all of the data has been imported.
)
You now have 1.8 million rows in a PowerPivot grid. You can sort and filter the data. You can also add new calculated columns in the grid.