About this lesson
Let's be honest, a huge amount of the world's data lives in Excel. We need to be able to pull these data sources into our Power Pivot models as well. In this lesson we will show how to collect data from Excel's Table object.
Download this lesson’s related exercise files.Getting Data from Excel Tables.xlsx
502.3 KB Getting Data from Excel Tables - Completed.xlsx
Getting Data from Excel Tables
An overview of importing data from an Excel table.
When to use
When you have tabular data on which you would like to build a query.
The general process is:
- If not already done, format your data as a proper Excel table (CTRL + T)
- Give your table a logical name (in the box on the very left side of the Table Tools --> Design tab)
- Create a new query --> From Table/Range
- Perform any transformations required to the table
- Click the Close & Load To... button
- When the Import Data dialog opens, select Only Create Connect --> Load This Data to the Data Model --> OK
Hints & tips
- Because Power Query will create a table named Table1, it is advised to manually create and name your tables, rather than let Power Query apply its defaults
- When you build relationships between two tables, the data type(s) of the related column(s) must be the same in both tables (text, whole number, date, etc.)
Lesson notes are only available for subscribers.