I was doing a seminar recently and many people at the company were getting reports downloaded as fixed width text files.

I encouraged them to start using the Get & Transform tools in Excel for cleaning this date. After the seminar, I created a text file with many of the issues that were in their workbooks.

Table of Contents
(0:00) Fixed Width Text File for Excel
(0:50) Excel Text Import Wizard UI for Marking Column Locations
(1:19) Finding column start locations for Power Query
(1:59) Importing to Excel from Text/CSV
(2:18) Power Query incorrectly guesses comma as delimiter
(2:42) Power Query make column wider
(2:55) Power Query removing top 6 rows
(3:30) Power Query Split by Position
(4:20) Power Query Use First Row as Headers
(4:30) Deleting non-data rows
(6:00) Converting blanks to null and Fill Down
(7:00) Marking date columns in Power Query
(7:46) Refreshing Power Query
(8:45) Inserting blank row at each change in employee
(11:17) Wrap-up