Excel - Power Query Import And Clean Fixed Width Text Files - Episode 2539

Microsoft Excel Tutorial: Cleaning Fixed Width Files in Power Query.

📊 Learn Excel from MrExcel Podcast, Episode 2359 - Importing Fixed Width Text Files 🚀

In this episode, Bill Jelen dives into the intricacies of importing fixed-width text files into Excel. The data is a bit messy, with blank rows and irregularities, making it a perfect candidate for the power of Get and Transform tools.

🔍 Key Steps:
Identifying Column Starting Points: Bill shows you how to determine where each column starts using Notepad++ and adjust for the zero-based nature of Get and Transform tools.
Get and Transform Data: Utilize the power of Power Query to import the text file and handle data detection challenges.
Cleaning and Transforming: Learn how to clean up junk rows, split columns, and efficiently manage your data using Power Query.
Filtering: Bill demonstrates effective filtering techniques, getting rid of irrelevant data and blank rows, ensuring a streamlined dataset.

⚙️ Power Query Efficiency: Witness how Power Query streamlines the data cleaning process, making it significantly faster and more efficient than traditional Excel methods.

🚀 Unlock the Potential of Power Query: Discover how to handle complex data imports with ease, saving time and effort. Whether you're dealing with irregularities or blank rows, Power Query is your go-to solution.

🎓 Excel Mastery Simplified: Bill's walkthrough simplifies complex data cleaning tasks, empowering you with the skills to tackle similar challenges effortlessly.

👍 Don't forget to Like, Subscribe, and Ring the Bell to stay updated on more valuable Excel insights! Share your thoughts and questions in the comments below.

Thank you for joining us on another insightful Netcast from MrExcel! 📊👨‍💼

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) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
learn how to split columns in power query
import fixed-width text files excel power query tutorial
import fixed-width text files excel tutorial
clean messy data in excel using power query
streamlining data cleaning with power query
excel fixed-width text file import tips
efficient data import with get and transform tools
handling irregularities in excel data import
importing and transforming fixed-width text files
power query for quick and easy data cleaning

[jessicadawson]

You just made my life so much easier. Thank you!!!!

[vlookup_tim]

30 years ago I was the guy spending that hour on a payroll text file in excel every month to reconcile the bank account at university. Now I’m using Power Query to cleanse a csv file from the bank in seconds for a small municipality. Progress!

[MilhouseBS]

Thanks! Thanks, hard to believe that Power Query can't figure out the fixed-width delimiters.

[Hyperion]

Hahaha this looks like the output from out 'new' system. I just spent an hour making a 'companion' table to the data using offsets and =MID only to find there's a secondary row "sometimes" that has important data as they have offset rows of data.

I'm going to use these tips to import the sheet two ways, one for the main row, one for the offset rows, then recombine them I Thanks MrExcel!

[toanh]

Before watching this video, I had a lot of trouble with reports like this, and didn't know how to use it with power query to automate it, many thanks Mr Excel

[ricos]

You can sort of do it in Power Query. Right click and select Duplicate on the name column and call the new column "Names" (just change in the formula bar). Then add another step in the applied steps window, and use:
=Table.Combine({#"Duplicated Column", Column"[#"Names"]), null, {"Names"})})
This step (and you can separate into a few if easier) takes a distinct list of all the names in the duplicated column and converts to a table, which is then combined with the original data, giving you a list of null lines with only the duplicate name field populated.

You can then sort this by your name column, but first you have to replace values again in the actual column that you wish to sort by, replacing null with "" (replace null in all other columns created by the combined tables too). Then sort by duplicated name column and then delete the duplicated name column and you're left with the data with spaces.

Obviously, the correct answer is that spaces are for satan.

[ronlo]

Thank you, this is great! I have been trying to solve this for over five years!

[chrism]

Thanks Mr Excel! Power Query continues to amaze me with all its capabilities

[nadermounir]

Thank you Mr Excel for this amazing video. 👏

[canirmalchoudhary]

Fill down with null values is cool feature

[zuhairalmutawa]

That is amazing. thank you for the new information.

[BillSzysz]

Empty row after each Name? No problem😁
WithEmptyRow = Table.Combine(Table.Group( MrExcelLastStep, {"Name"}, {{"tbl", each _ & #table({"Name"}, {{null}}), type table}})[tbl])
in
WithEmptyRow

[myresearchonmicroornithopt]

Hey, Thanks for the Video, It was very helpful. But I couldn't automate the report with this solution, As I am getting different position of column every day, Any idea for that??

[aaleyr]

This was amazing . But my file has headers spread into 2 rows and data is 2 rows seperated by a blank row . it runs into many pages . how to clean it ?

[ExcelInstructor]

Can I have this file?
I would love to play around with it in PQ

[GeertDelmulle]

Hey Mr.Excel,
You can insert the blank row in Power Query quite easily:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
DuplicateName = Table.DuplicateColumn(Source, "Name", "Name2"),
Combine = Table.Combine({DuplicateName, Table.Distinct(DuplicateName[[Name2]])}),
SortRows = Table.Sort(Combine, {{"Name2", Order.Ascending}, {"Value", Order.Ascending}}),
RemoveColumnRow = Table.Skip(Table.RemoveColumns(SortRows, {"Name2"}), 1)
in
RemoveColumnRow