May 14, 2014

I was working with Power Query for Excel and needed to parse a tab separated file.  Unfortunately there isn’t a TSV option directly in Power Query.  I couldn’t find much information on how to tackle this so I’ll post here.

The first thing I ended up doing was importing the tab separated file as a CSV.  This added three steps to Power Query.  The first step looked like this. 

= Csv.Document(File.Contents(“<FILE LOCATION>”),null,{0,42,53},null,1252)

If you look at the documentation for Csv.Document, you’ll see that the third parameter is the delimiter.  Unfortunately something like “/t” doesn’t work in PQ so I had to figure out what it is.

If you use #(tab), that is what you need to “escape” tab and send the value to the function.  You’ll need to have quotes around it like this:

= Csv.Document(File.Contents(“<FILE LOCATION>”),null,”#(tab)”,null,1252)

I found that this messed up the next steps so I ended up creating a blank query and adding this as the source step.  This set up the FirstRowAsHeader step and the ChangedType step.