Excel.Workbook

Updated on

Excel.Workbook is a Power Query M function that returns the contents of an Excel workbook with options to customize the processing of headers and column types. The function returns a table containing the workbook content.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Excel.Workbook(
   workbook as binary,
   optional useHeaders as any,
   optional delayTypes as nullable logical,
) as table

Description

Returns the contents of the Excel workbook.

  • useHeaders can be null, a logical (true/false) value indicating whether the first row of each returned table should be treated as a header, or an options record. Default: false.
  • delayTypes can be null or a logical (true/false) value indicating whether the columns of each returned table should be left untyped. Default: false.
If a record is specified for useHeaders (and delayTypes is null), the following record fields may be provided:
  • UseHeaders: Can be null or a logical (true/false) value indicating whether the first row of each returned table should be treated as a header. Default: false.
  • DelayTypes: Can be null or a logical (true/false) value indicating whether the columns of each returned table should be left untyped. Default: false.
  • InferSheetDimensions: Can be null or a logical (true/false) value indicating whether the area of a worksheet that contains data should be inferred by reading the worksheet itself, rather than by reading the dimensions metadata from the file. This can be useful in cases where the dimensions metadata is incorrect. Note that this option is only supported for Open XML Excel files, not for legacy Excel files. Default: false.

Examples

Return the contents of Sheet1 from an Excel workbook.

Excel.Workbook( File.Contents( "C:Book1.xlsx" ), null, true ){[Item="Sheet1"]}[Data]

 /* Output: 
Table.FromRecords( {
    [Column1 = "ID", Column2 = "Name", Column3 = "Phone"],
    [Column1 = 1, Column2 = "Bob", Column3 = "123-4567"],
    [Column1 = 3, Column2 = "Pam", Column3 = "543-7890"],
    [Column1 = 2, Column2 = "Jim", Column3 = "987-6543"]
} )
 */ 

Other functions related to Excel.Workbook are:

BI Gorilla Blog

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/excel-workbook