BC.QUERY
Overview
Use the BC.QUERY function to return a list of objects of a specified Business Central object type (as either an Excel array or an Excel table), filtered to match provided criteria.
Syntax
=BC.QUERY(
ConnectionName,
Object,
Filter,
Select,
IncludeHeader,
Settings,
Api
TableOutputCell
)
Arguments
The BC.QUERY function uses the following arguments:
Argument | Required/Optional | Description |
| Required | The name of the connection, as specified in the Connection Manager |
| Required | Business Central object name (e.g., Use the BC.EXPANDOBJECTRANGE function to retrieve the list of Business Central objects. Certain objects (for example, |
| Optional | OData4 query based on the fields in the object. OData4 operators are supported, including: |
| Optional | Comma-separated list of object fields to be included in the resulting dataset. If this argument is empty, all the Fields from the Object will be returned. |
| Optional |
|
| Optional | Two-dimensional array
|
| Optional | API to be referenced for Object names.
|
| Optional | If the argument is specified, the function output is represented as an Excel table, and the first column in the If the argument is omitted, the result is returned as an array. |
Excel Online
Important: Loading large datasets with BC.QUERY is not performant in Excel Online due to the limitations of the Excel platform in the browser. If your dataset contains more than approximately 100,000 records, we strongly recommend using a desktop version of Excel 365 for Windows or Mac OS.
Examples
First ten records
=BC.QUERY(
"BC",
"generalLedgerEntries",
,
,
,
10
)
Description: Returns the first 10 records found for the Object generalLedgerEntries
.

Filters and column selection
=BC.QUERY(
"BC",
"salesinvoices",
"sellToState eq 'ON' and status eq 'Paid'",
"number,customerNumber,invoiceDate,totalAmountExcludingTax"
)
Description: Returns the number
, customerNumber
, invoiceDate
and totalAmountExcludingTax
fields for all salesInvoices
object with the status Paid
, issued to customers in the province of Ontario.

Querying objects that require filtering
Certain Business Central objects require additional filtering to be queried. For instance, when you try to query the journalLines
object without filters, you will receive an error:

This object requires you to provide a filter for the batch ID
or journal ID
field, for instance:
=BC.QUERY(
"BC",
"journallines",
"journalid eq 42009fff-9023-ef11-840f-6045bde9c820"
)
Description: Returns all entries for the journalLine
object with journal ID
equal to 42009fff-9023-ef11-840f-6045bde9c820
for the connection BC
