Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Download this lesson’s related exercise files.Getting Data from Databases.xlsx
63.4 KB Getting Data from Databases - Completed.xlsx
501.4 KB Mulligans.accdb
Getting Data from Databases
An overview of importing data from a database.
When to use
When you want to bring data from a database into Power Query and load it into Power Pivot.
The general process is:
- Begin a new query --> Get Data --> From Database --> choose your Database
- Locate the database you want to use and log in (if necessary)
- When the Navigator dialog opens, select the desired database and choose Edit to launch the Power Query Editor
- Rename the query in the Query Settings pane on the right
- Remove any columns that are not needed by one of the following three methods:
- Selecting the column and clicking Remove Columns
- Selecting the column and pressing the DELETE key
- Right clicking the column and selecting Remove
- 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
- If your version of Excel contains a From Access button directly on the ribbon, DO NOT use it. This is an old legacy connector that will be hidden in Excel 2016 and higher
- Quickly replace values in an entire column by:
- Right clicking the column --> Replace Values -->
- Enter current value --> Enter the value to replace it with --> OK
Lesson notes are only available for subscribers.