About this lesson
Data often lives inside Excel tables. But even if it doesn't, Power Query will create one for you.
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.Getting data from Excel tables.xlsx
20.6 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.
- If not already done, format your data as a proper Excel table (CTRL + T)
- Ensure your table has a readable name (shown on the left side of the Table Tools Design tab)
- Create a new query --> From Table/Range
- Dates are stored based on the date serial number, so you never need to use Locale for these
- Dates do get a DateTime datatype applied
- Replace the DateTime datatype by clicking the calendar icon on the top left of the column --> Date
Changing the name of a table used by Power Query
- Table names are hardcoded into queries, and are not updated when you change the name of the table
- When you rename a query, it will break Power Query’s ability to refresh
- To fix a broken reference, edit the query, select the Source step and replace the name of the old table in the formula bar with the name of the new table
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
- There is no need to promote headers when a table in imported, because tables already have defined headers that Power Query uses
- You cannot have multiple tables in a file with the same name. If you try to use the same name multiple times, a value will be appended to the name to keep it distinct
Lesson notes are only available for subscribers.