A couple of weeks back, I received a comment on my “Force Power Query to Import as a Text File” blog post.  Rudi mentioned that he’d really like to see a method that would let us Treat Consecutive Delimiters as One; something that is actually pretty easy to find in Excel’s old Import as Text file feature.

…the option for “Treat consecutive delimiters as one” should be made available too. The text import eventually imported but the info was all out of sync due to the tabbed nature of the data – names longer and shorter cause other data to not line up.

So let’s take a look at this issue today.  You can access and download the sample file I’ll be working with from this link.

The issue at hand

The contents of the text file, when viewed in Notepad, are fairly simple:

But the challenge shows up when we go to import it into Power Query.  Let’s do that now:

• Power Query –> From File –> From Text
• Browse to where you stored the file and double click it to Open it up

The first indication that we have an issue is that we’ve got a single column, the second is that it’s a bit scattered:

Well no problem, it’s a Tab delimited file, so we’ll just split it by Tabs, and all should be good, right?

• Home –> Split Column –> By Delimiter
• Choose Tab and click OK

Uh oh…  What happened here?

The issue is that we’ve got multiple delimiters acting between fields here.  They look like one, but they’re not.  Between Date and Vendor, for example, there are two quotes.  But between the Date values and the Vendor values only one Tab is needed to line it up in the text document.  The result is this crazy mess.

Approaches to the Issue

I can see three potential routes to deal with this problem:

1. We could replace all instances of 2 Tab’s with a single Tab.  We’d need to do that a few times to ensure that we don’t turn 4 Tabs into 2 and leave it though.
2. We could write a function to try and handle this.  I’m sure that this can be done, although I decided not to go that route this time.
3. We could split the columns using the method that I’m going to outline below.

Before we start, let’s kill off the last two steps in the “Applied Steps” section, and get back to the raw import that we had at the very beginning.  (Delete all steps except the “Source” step in the Applied Steps window.)

How to Treat Consecutive Delimiters As One

The method to do this is a three step process:

1. Split by the Left Most delimiter (only)
2. Trim the new column
3. Repeat until all columns are separated

So let’s do it:

• Select Column1
• Split Column –> By Delimiter –> Tab –> At the left-most delimiter

Good, but see that blank space before Vendor?  That’s one of those delimiters that we don’t want.  But check what happens when you trim it…

• Select Column1.2
• Transform—>Format –> Trim

And look at that… it’s gone!

Let’s try this again:

• Select Column1.2
• Split Column –> By Delimiter –> Tab –> At the left-most delimiter

And trim the resulting column again:

• Select Column1.2.2
• Transform—>Format –> Trim

And we’re good.  Now to just do a final bit of cleanup:

• Transform –> Use First Row as Headers
• Name the Query

And we’re finished:

Easy as Pie?

In this case, yes it was.  Easy, if a bit painful.  But what about spaces?  If this file was space delimited I couldn’t have done this, as my Vendors all have spaces in them too.  So then what?

I’d modify my procedure just a bit:

1. Replace all instances of double spaces with the | character
2. Split by the left most | character
3. Replace all | characters with spaces
4. Trim the new column
5. Repeat until finished

Final Thought

I haven’t received too much in the way of data like this, as most of my systems dump data into properly delimited text or csv files, but I can certainly see where this is an issue for people.  So I totally agree that it would be nice if there were an easier way to treat consecutive delimiters as one in Power Query.

I do like the versatility of the choices we have currently, but adding this as another option that works in combination with the existing ones would be fantastic.

Power Query team: here’s my suggestion…

🙂