Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Connecting to external data sources (such as databases), and pulling the data into Excel.
Multiple versions of this lesson are available, choose the appropriate version for you:
Using External Data
Pulling external data into Excel and PivotTables.
Where/when to use the technique
When part or all of the data you need to build your reports is stored in an external file or database.
Sourcing data with Power Query
- Go to the Data tab > Get Data > choose the file type you want to import
- Browse to your data source, select it and click Import
- Choose the table(s) or query you wish to import and click the button to the left of Cancel
- Perform any transformations needed
- Choose to land it in a table or a PivotTable
- If you landed it to a table first, go to the Insert menu and add a new PivotTable
- Layout your PivotTable as desired
- Refresh your PivotTable from the database
Key points to remember
- You can bring in related records from other available tables and queries from the database without having to write a VLOOKUP
Landing data in a table first
- You can “see” the data in the worksheet so you can review it
- You can easily write formulas to manipulate, change or add to the data
- Means that your data set must be less than 1,048,575 rows
- This is the maximum number of rows in an Excel worksheet, not including 1 row for the header
Landing data in a PivotTable first
- You can’t “see” the data in the worksheet so you can review it
- You can’t easily write formulas to manipulate, change or add to the data
- Means that your data set can be more than 1,048,575 rows
- The PivotTable summarizes it before it lands in the worksheet
Lesson notes are only available for subscribers.