Power BI Blog: Text isn't Limiting! - Part 1

Welcome back to Power BI Tips.

There’s a significant difference between CSV files and fixed-width text files. CSV uses the comma to delineate the fields, but text files rely on spacing for formatting. Fixed-width data output is quite common in computing systems and programs that originated pre-2000.

Massaging text files is always a bit of work however the functions are more user friendly in the Query Editor than in Excel. For today’s example, we will be using the Historical National Population Estimates from the US Census Bureau. Find it here – the “National Totals” download at the top of the page.

Opening the file in any text editor, the format is very clear:

Time to import it into Power BI and get right into it. Click “Get Data” and select “Text/CSV”.

The file dialog will come up, select the text file from where it was saved.

You’ll see the following screen pop up:

Hit “Edit” to clean the data and make it useful.

Looking at the top, we can remove the first 7 rows (this was covered here) to get to the data headers.

Looking at the bottom, we’ll need to remove the last 25 rows as well:

This is the same as “Remove Top Rows”, but the option is underneath it in the drop down of “Remove Rows”.

The data should look like this:

It's starting to shape up nicely.

Next week we'll look at further fixing the data with the Query Editor using it to split the data into columns and fixing errors.

See you next time for more Power BI Tips.