OpenBI Docs Buy

TOCSV

A table's first rows (10 by default) as CSV text, with a header and a delimiter of your choice.

Group: Table manipulation. Compatible with DAX.

Syntax

TOCSV ( <Table> [, <MaxRows>] [, <Delimiter>] [, <IncludeHeaders>] )
ParameterTypeDescription
TableTableThe rows to write.
MaxRowsNumber
optional		How many rows to write: 10 when left out, any negative number for all of them.
DelimiterText
optional		What goes between two values, one character such as a semicolon: a comma when left out.
IncludeHeadersBoolean
optional		TRUE (when left out) to start with the columns' names, FALSE to leave them out.

Returns

Text. The rows as CSV: one line per row, the values separated by the delimiter.

Remarks

  • Lines are separated by a line break; the header, when there is one, comes first.
  • Column names are written as in a query: 'Holdings'[Bond] for a column of the model, [Name] for one made in the query.
  • A value that holds the delimiter, a quote or a line break is put in quotes, with each quote inside it doubled.
  • Numbers are written plainly with a point for decimals, dates as month/day/year (with AM or PM when there is a time), TRUE and FALSE as words, and a blank as nothing.
  • Rows are written in the order the table has them. To choose the order, build the text with CONCATENATEX instead.
  • With no row to write, the result is the header followed by a line break, or BLANK when the header is left out.
  • It is meant for looking at a table, such as a variable, while you write a measure.

Examples

A small table as CSV

The Cider line has no liters, so its value is empty.

EVALUATE
VAR Orders =
    DATATABLE (
        "Product", STRING, "Liters", INTEGER, "Price", DOUBLE,
        { { "Pils", 120, 38 }, { "IPA", 80, 64 }, { "Cider", BLANK (), 52 }, { "Stout", 45, 70 } }
    )
RETURN
    ROW ( "CSV", TOCSV ( Orders ) )
CSV
[Product],[Liters],[Price] Pils,120,38 IPA,80,64 Cider,,52 Stout,45,70

The lines of the text are separated by line breaks, which the result table shows as spaces.

All rows, a semicolon, no header

The name with a semicolon is put in quotes, and so is the one with quotes, which are doubled.

EVALUATE
VAR Orders =
    DATATABLE (
        "Product", STRING, "Liters", INTEGER,
        { { "Pils", 120 }, { "IPA; strong", 80 }, { "Say ""hi""", 5 } }
    )
RETURN
    ROW ( "CSV", TOCSV ( Orders, -1, ";", FALSE ) )
CSV
Pils;120 "IPA; strong";80 "Say ""hi""";5

The lines of the text are separated by line breaks, which the result table shows as spaces.

Columns of the model

Three rows: the names carry the table, the dates are month/day/year, and the third bond has no price.

EVALUATE
ROW (
    "CSV", TOCSV ( SELECTCOLUMNS ( Holdings, Holdings[Bond], Holdings[Maturity], Holdings[Price] ), 3 )
)
CSV
'Holdings'[Bond],'Holdings'[Maturity],'Holdings'[Price] Government 2028,3/2/2028,97.84 Government 2033,3/15/2033,92.15 Municipal 2031,9/1/2031,

The lines of the text are separated by line breaks, which the result table shows as spaces.

TOJSON, CONCATENATEX, EVALUATEANDLOG, TOPN.

Last updated · OpenBI Studio 0.3.74