Power BI Connector for Microsoft Excel Online

Build 23.0.8839


When you connect, the 本製品 discovers the available columns and reflects both changes in Microsoft Excel Online and configuration changes you make with the following connection properties. See the following sections to configure aspects of column discovery specific to Microsoft Excel Online.

Configuring Column Name Detection

You can set Header to detect column names in a header row or you can reference columns alphabetically.

Header=True (Default)

Below are more specifics on how the 本製品 detects column names from a header row when Header is set to true.

  • The 本製品 determines columns from the first row of the worksheet. If no values are provided for the first row of the worksheet, the 本製品 creates unique, alphabetized column names that are available only within the scope of that request.
  • Headers should not contain special characters.
  • Due to a limitation of the Excel Online API, all column headers must be nonempty.


When Header is set to false, alphabetical column names will be dynamically assigned.

Primary Key

The 本製品 reports the row number as the primary key. The Id column for each row represents the row number from the top of the sheet.

For example, if you specify a range A3:E6, rows 3, 4, 5, and 6 will be returned.

Configuring Column Value Parsing

The ValueRenderOption and ValueInputOption connection properties can be used to configure how the 本製品 parses the Microsoft Excel Online cell formats in SELECT, INSERT and UPDATE operations.

Value render options for SELECT operations

Set the ValueRenderOption to configure how the 本製品 renders values in the output of a SELECT operation. The available value render options are as below.

  • FormattedValue: Values will be calculated and formatted in the output according to the cell's formatting. Formatting is based on the workbook's locale, not the requesting user's locale. This is the default ValueRenderOption.
  • UnformattedValue: Values will be calculated but not formatted in the output.
  • Formula: Values will not be calculated and instead the output will include the formulas. For this reason, every column containing a formula will be exposed as String type regardless of the set TypeDetectionScheme.

Value input options for INSERT and UPDATE operations

Set the ValueInputOption to configure how the 本製品 parses values the user submits to the Microsoft Excel Online source for INSERT and UPDATE operations. The available value input options are as below.

  • UserEntered: The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc., following the same rules that are applied when entering text into a cell via the Microsoft Excel Online UI. This is the default ValueInputOption.
  • Raw: The values the user has entered will not be parsed and will be stored as-is.

Configuring Data Type Detection

Set the TypeDetectionScheme to configure the data type detection method. The available type detection schemes are as below.

  • RowScan: The 本製品 will scan rows to heuristically determine the data type. The number of rows the 本製品 will scan can be determinated by the RowScanDepth property. This is the default TypeDetectionScheme.
  • ColumnFormat: The 本製品 will report the data type based on the cell format of the column. This option is applicable only for the 'UnformattedValue' and 'Formula' ValueRenderOption-s. If the ValueRenderOption is set to 'FormattedValue', all columns will be exposed as String types.
  • None: The 本製品 will return all columns as the string type.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839