DuckDB.Query OpenBI

A table from DuckDB SQL, run by the project's own engine: files, joins and DuckDB's functions in one query.

Group: Databases and lakes. OpenBI’s own: Power Query M has no function of this name.

Syntax

DuckDB.Query(query as text) as table

Returns

table. The statement's rows, its columns typed from DuckDB's types.

Remarks

• Files are read by their paths: read_csv, read_parquet and read_json, a pattern such as *.csv for many files at once.
• Whole numbers (INTEGER, BIGINT) are Int64.Type, DOUBLE and DECIMAL numbers, VARCHAR text, DATE dates, TIMESTAMP datetimes, TIMESTAMPTZ datetimezones and BOOLEAN logicals.
• The steps after it fold into the same SQL.
• On OpenBI Server a query is one statement that reads: nothing is written or changed. In the studio's preview, a statement that installs or loads a DuckDB extension is refused, and DuckDB's spatial functions aren't part of this version.
• It is OpenBI's: a Power BI project kept working in Power BI can't use it. In Transform data, a whole query can also be DuckDB SQL, step by step.

Compared with Power Query

An OpenBI function: Power Query has none like it, so a query that uses it runs in OpenBI only, not in Power BI Desktop or the Power BI service.

Examples

Every month's file, summed by shop

DuckDB.Query("SELECT Shop, SUM(Amount) AS Sales FROM read_csv('C:\Data\orders\*.csv') GROUP BY Shop ORDER BY Shop")

The pattern reads the folder's two CSV files, not the archive folder below it.

Rows written in SQL

DuckDB.Query("SELECT * FROM (VALUES ('Oslo', 120.5), ('Bergen', 95.0)) AS t(Shop, Sales)")

A few days and their weekdays

DuckDB.Query("SELECT CAST(d AS DATE) AS Day, dayname(d) AS Weekday FROM range(DATE '2026-03-01', DATE '2026-03-04', INTERVAL 1 DAY) AS t(d)")

Related functions

Odbc.Query, Parquet.Document, Csv.Document, OpenBI.Lake.

Last updated 8 November 2026 · OpenBI Studio 0.3.79