About this lesson
Importing data from a text file into an Excel table using Power Query.
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.Importing Data from a Text File.xlsx
21.8 KB Importing Data from a Text File - Completed.xlsx
35.2 KB Sales - Nov.txt
599 B Sales - Dec.txt
598 B Practice.txt
Importing Data from a Text File
An overview of importing and cleaning data from a text file using Power Query.
When to use
When you use a text file as a data source for your query and must clean up the (often) messy results so the data can be loaded into Excel.
- Create a new query --> From File --> From Text/CSV --> browse for the file to import
- When greeted by the preview window, click Edit
Helpful tips for cleaning up text files
Removing irrelevant rows
- Go to Home --> Remove Rows
- Can be used to remove Top x, Bottom x, or even alternating rows
Tips for removing characters from text
- Leading and trailing whitespace: right click the column header --> Transform --> Trim
- Internal whitespace: right click the column header --> Replace Values --> replace 2 spaces with 1
- Non-printing characters: right click the column header --> Transform --> Clean
- Use Promote First Row to Headers to give the columns decent names
- Rename column headers by double-clicking the header name and typing a replacement
- To change the data type, click on the symbol and choose the data type you want
- Use the filter arrows to the right of the column header to remove rows
- Filters can be used to remove values (including nulls) but not errors
Hints & tips
- You can go back through the steps applied to the query in the Query Settings window
- Any query step can be deleted by clicking on the “X” to the left of the query step
Lesson notes are only available for subscribers.