Csv.Document
A CSV file or text split into a table: one row per line, one column per field, every value as text.
Group: Files and documents. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Csv.Document(source as any, optional columns as any, optional delimiter as any, optional extraValues as nullable number, optional encoding as nullable number) as table| Parameter | Type | Description |
|---|---|---|
source | any | The file's bytes (File.Contents, Web.Contents ...) or the CSV as text. |
columns | any optional | How many columns, a list of their names, a table type, or a record of options: Delimiter, Columns, Encoding and QuoteStyle. |
delimiter | any optional | The text between fields; a comma unless you give another. |
extraValues | nullable number optional | What to do with fields beyond the last column (ExtraValues.Error, .Ignore, .List). |
encoding | nullable number optional | The file's text encoding, such as TextEncoding.Utf8 (65001, the default), TextEncoding.Windows (1252) or another code page's number. |
Returns
table. Columns named Column1, Column2 ... (or the names you gave), every value text; a field a line lacks is null.
Remarks
- The first line is a row like the others:
Table.PromoteHeadersmakes it the headers, as the query Get data writes for a CSV file does, followed by a Changed Type step. - Without a column count, the table has as many columns as the first line has fields. A shorter line gets null in the columns it lacks; an empty field between two delimiters is empty text.
- With QuoteStyle.Csv (the default) a quoted field may hold the delimiter and line breaks, and two quotes in a row stand for one. QuoteStyle.None ends a line at every line break.
- A file is read by DuckDB, so the steps after it (filters, chosen columns, groups) fold into the SQL DuckDB runs on the file. A file that isn't plain UTF-8 (Windows 1252 with its € and ø, UTF-16, or bytes that aren't valid UTF-8) is read by OpenBI itself, as Power Query reads it.
- The encodings are those of
Text.FromBinary: a byte order mark at the start wins over the encoding given, and bytes that aren't valid UTF-8 come out as the replacement character.
Examples
A file as it is
Each line is a row and every value is text. The last line has two fields only.
Csv.Document(File.Contents("C:\Data\sales.csv"))| Column1 | Column2 | Column3 | Column4 |
|---|---|---|---|
| Product | Region | Sales | Note |
| Chair | North | 100 | (empty text) |
| Desk | South | 250 | big, heavy |
| Chair | South | 75 | say "hi" |
| Lamp | North | null | null |
A quoted field keeps its comma (big, heavy), and two quotes inside quotes read as one.
Options as Get data writes them, then headers
let
Source = Csv.Document(File.Contents("C:\Data\sales.csv"), [Delimiter = ",", Columns = 4, Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
Headers = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
in
Headers| Product | Region | Sales | Note |
|---|---|---|---|
| Chair | North | 100 | (empty text) |
| Desk | South | 250 | big, heavy |
| Chair | South | 75 | say "hi" |
| Lamp | North | null | null |
Sales is still text: a Changed Type step (Table.TransformColumnTypes) makes it a number.
CSV written as text, with semicolons
Csv.Document("Shop;Sales#(cr,lf)Oslo;120#(cr,lf)Bergen;95", [Delimiter = ";"])| Column1 | Column2 |
|---|---|
| Shop | Sales |
| Oslo | 120 |
| Bergen | 95 |
Related functions
File.Contents, Table.PromoteHeaders, Table.TransformColumnTypes, Lines.FromBinary, Folder.Files.