digest
Short Summary
Main recommendation: Use Excel 2016's Data ribbon, Get & Transform group (Power Query), to import all text files from a folder with a single query that can be refreshed when new files are added. The video shows this with two tab-separated .txt files, filtering out .csv files, cleaning columns, and loading the result as a table on an existing worksheet. The creator notes that Power Query used to be a separate download that appeared as its own ribbon tab, while in Excel 2016 it is built in and found under Data.
Key Points
- Power Query is located in Excel 2016 under Data > Get & Transform, rather than a separate Power Query tab as in earlier versions.
- A "From File > From Folder" query imports every file in the chosen folder, so the workflow handles multiple files at once.
- Filtering the file-extension column to .txt keeps tab-separated text files and excludes .csv files.
- Adding more .txt files to the folder and refreshing in Excel automatically pulls them into the query.
- Because the first file's headers are promoted automatically, the query must use a column with few unique values to confirm the correct field names come from the right file.
Details Worth Noting
- Concrete instructions:
- Go to Data > Get & Transform > New Query > From File > From Folder.
- Click Browse, select the unzipped Start Files folder containing the text files, then click OK and OK again to open the Power Query Editor.
- Give the query a descriptive name (the example uses "Import Great Data from Text Files").
- Filter the file extension column to keep only .txt files and exclude .csv files.
- Right-click the Content column and choose Remove Other Columns.
- Expand the Content column (the double downward arrow); Power Query promotes the first row to headers and changes data types, with text fields set to Text and numeric fields set to Decimal Number.
- Choose a column with few unique records, click its filter, and click Load More so the unique list includes all files; then deselect the field-name record from the second file so only the correct three field names remain.
- Click OK, then choose Close & Load To, select Table, choose Existing Worksheet (A1 used in the example), and click Load.
- The speaker says the same approach was used in Excel 2013 videos, where the From File/From Folder option was a button in the Power Query ribbon tab.
- The Only Create a Connection option (sending data to the data model) is mentioned as an alternative covered in an earlier video, but this video loads a table.
Bottom Line
Excel 2016 includes Power Query under the Data tab, and the video demonstrates that a folder-based query can combine text files and refresh automatically when new ones are added.
