Text/CSV

Summary

Release State: General Availability

Products: Power BI Desktop, Power BI Service (Enterprise Gateway), Dataflows in PowerBI.com (Enterprise Gateway), Dataflows in PowerApps.com (Enterprise Gateway), Excel

Function Reference Documentation: File.Contents, Lines.FromBinary, Csv.Document

Capabilities supported

  • Import

Load from Text/CSV File

To load a local text or csv file, all you need to do is select the Text/CSV option in the connector selection. This will launch a local file browser and allow you to select your text file.

Text file selection

Power Query will treat CSVs as structured files with a comma as a delimiter—a special case of a text file. If you choose a text file, Power Query will automatically attempt to determine if it has delimiter separated values, and what that delimiter is. If it can infer this, it'll automatically treat it as a structured data source.

Unstructured Text

If your text file doesn't have structure you'll get a single column with a new row per line encoded in the source text. As a sample for unstructured text, you can consider a notepad file with the following contents:

Hello world.
This is sample data.

When you load it, you're presented with a navigation screen that loads each of these lines into their own row.

Loading data from a simple unstructured text file

There's only one thing you can configure on this dialog, which is the File Origin dropdown select. This lets you select which character set was used to generate the file. Currently, character set is not inferred, and UTF-8 will only be inferred if it starts with a UTF-8 BOM.

File culture selection for Text/CSV

CSV

You can find a sample CSV file here.

In addition to file origin, CSV also supports specifying the delimiter, as well as how data type detection will be handled.

Loading data from a csv file

Delimiters available include colon, comma, equals sign, semicolon, space, tab, a custom delimiter (which can be any string), and a fixed width (splitting up text by some standard number of characters).

Delimiter selection for a csv file

The final dropdown allows you to select how you want to handle data type detection. It can be done based on the first 200 rows, on the entire data set, or you can choose to not do automatic data type detection and instead let all columns default to 'Text'. Warning: if you do it on the entire data set it may cause the initial load of the data in the editor to be slower.

Data type inference selection for a csv file

Since inference can be incorrect, it is worth double checking settings before loading.

Structured Text

When Power Query can detect structure to your text file, it'll treat it as a delimiter separated value file, and give you the same options available when opening a CSV—which is essentially just a file with an extension indicating the delimiter type.

For example, if you save the following below as a text file, it'll be read as having a tab delimiter rather than unstructured text.

Column 1	Column 2	Column 3
This is a string.	1	ABC123
This is also a string.	2	DEF456

Loading data from a structured text file

This can be used for any kind of other delimiter based file.

Editing Source

When editing the source step, you'll be presented with a slightly different dialog than when initially loading. Depending on what you are currently treating the file as (that is, text or csv) you'll be presented with a screen with a variety of dropdowns.

Editing the source step on a query accessing a CSV file

The Line breaks dropdown will allow you to select if you want to apply linebreaks that are inside quotes or not.

Editing the line break style for a CSV file

For example, if you edit the 'structured' sample provided above, you can add a line break.

Column 1	Column 2	Column 3
This is a string.	1	"ABC
123"
This is also a string.	2	"DEF456"

If Line breaks is set to Ignore quoted line breaks, it will load as if there was no line break (with an extra space).

Loading of a CSV file with quoted line breaks ignored

If Line breaks is set to Apply all line breaks, it will load an extra row, with the content after the line breaks being the only content in that row (exact output may depend on structure of the file contents).

Loading of a CSV file with quoted line breaks applied

The Open file as dropdown will let you edit what you want to load the file as—important for troubleshooting. Note that for structured files that aren't technically CSVs (such as a tab separated value file saved as a text file), you should still have Open file as set to CSV. This also determines which dropdowns are available in the rest of the dialog.

Changing the type of file

Troubleshooting

Loading Files from the Web

If you are requesting text/csv files from the web and also promoting headers, and you’re retrieving enough files that you need to be concerned with potential throttling, you should consider wrapping your Web.Contents call with Binary.Buffer(). In this case, buffering the file before promoting headers will cause the file to only be requested once.

Unstructured text being interpreted as structured

In rare cases, a document that has similar comma numbers across paragraphs might be interpreted to be a CSV. If this happens, edit the Source step in the Query Editor, and select Text instead of CSV in the Open File As dropdown select.