Power Query: Unlimited Importing of Non-Delimited Text Files
10 March 2017
Welcome to our Power Query blog. Today I look at importing data from non-delimited text files.
As a programmer, I was often asked to produce delimited versions of reports which could easily be picked up by Excel. Whilst it is possible to clean non-delimited files in Excel, it’s a laborious and repetitive process, which is why it made more sense to pay for a programmer to automate it. Power Query is a free method of cleaning up these files, and since the steps are recorded, it can be reapplied.
I begin by creating a new query from my file, as shown below:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image1.png/f69a0d9fd50a89ab6f02b9f08555dc2b.jpg)
I browse to my file and select it. Power Query has had a valiant effort at delimiting my data as there happened to be a colon between the ‘Name’ title and the name, but most of it is in one column. My goal is to split the data into more columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image2.png/11ac921c4a078e6b2fd8cf0c6140873d.jpg)
The first four rows don’t include any data I want, so I am going to edit and get rid of these. In the ‘Home’ tab, there is a section to do just this:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image3.png/6fdfdf55ed0962ecf69a51ccf8164f69.jpg)
I remove the first four rows and then the blank row after the Name row. I could have done this in one step by just removing the blank rows, as shown below:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image4.png/d8bf31c64901869a2734b7bc875b948e.jpg)
Now I want to populate Column2 all the way down. I described this process in detail in Getting Started where I replace the blank names with null and then fill down. I can then remove the first row and rename the column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image5.png/8993de5dd33606568b75339f6b735b7f.jpg)
In order to make sure my data in Column1 is as clean as it can be, I trim and clean it in order to remove any leading and trailing spaces, along with any annoying escape codes. Right-clicking the column reveals these options:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image6.png/2cb10be0bbe7a33ac953b84a5da840a0.jpg)
The next step is to break up the data in column 1, and to do this, there is a ‘Split Column’ option in the ‘Home’ section:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image7.png/4b4e4421d8d33d58a7d1f89516ab5951.jpg)
I make a guess at where to split my data – since I can edit the step by clicking on the gear next to the step in the ‘APPLIED STEPS’ window, I can adjust this until my data looks right.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image8.png/466c2418c659e33f504c94dd572b3916.jpg)
My date data looks good. For my new column Column1.2, I use the ‘£’ sign as a delimiter instead of using a count because my expense codes vary in length:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image9.png/eff5101c132500048eb94a62700330f7.jpg)
The end result looks promising.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image10.png/67e2ce26e5e71e467c970974dc0bb7e3.jpg)
There’s not much point trying to use my top row as headings, so I delete the top row and rename the columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/may/pq-23-image11.png/0f4e3b5604b68ebfbca3c4b3d27ef595.jpg)
The data looks ready to load. This query can be applied to another delimited expenses text file that comes through in a similar format, and the steps can be adjusted as required.
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!