#table - Table Function | Power Query M

#table is a Power Query M function that creates a table value from columns and rows.

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

Syntax

#table(
   columns as any,
   rows as any,
) as any

Description

The #table function constructs a table from specified columns and rows. The columns parameter can be a list of column names, a table type, a column count, or null. The rows parameter is a list of lists, where each sublist represents the values for a row in the table.

Examples

Let’s see how #table works with some practical examples.

Creating an Empty Table

To create an empty table, use the #table function with two empty lists:

#table( { }, { } )

Creating a Table with Two Columns and Rows

You can generate a table with two columns by defining two rows of data. The following examples do not specify column names or assign data types:

#table(
     null,              // not specifying any column name
     { 
        { 1, "Apple" }, // specifies values for first row
        { 2, "Prume" }  // specifies values for second row
     }
)

Alternatively, you can define the number of columns without providing names:

#table(
     2,                 // specifying number of columns, no name
     { 
        { 1, "Apple" }, // determines values first row
        { 2, "Prume" }  // determines values second row
     }
)

Create table using #date without column names or types specified

Creating a Table with Named Columns

In most instances, being more specific with your table’s structure is useful. To illustrate, you might want to assign specific column names. These can be supplied as a list in the first argument.

#table( 
     { "ProductKey", "Product" }, // defines column names
     {
        { 1, "Apple" },           // determines values first row
        { 2, "Prume" }            // determines values second row
     } 
)

Create table using #date specifying column names but no data types

Creating a Table with Data Types

For the best results, consider specifying data types while creating a column. The first argument accommodates complex data types where you can specify the data type of each column. The following code returns a table with defined column names and data types.

#table( 
     type table[ ProductKey = Int64.Type, Product = Text.Type ],
     { 
        { 1, "Apple" },
        { 2, "Prume" }
     }  
)

Create table using #date and specifying column names and data types

Creating a Table with Multiple Data Types and Rows

Here’s a more advanced example demonstrating how to create a table with specified column data types and multiple rows generated from a list of dates:

#table(
  type table [
    Date =          date, 
    Year =          Int64.Type, 
    Month =         Int64.Type, 
    MonthName =     text, 
    Day =           Int64.Type, 
    DayOfWeek =     Int64.Type, 
    DayOfWeekName = text, 
    Quarter =       Int64.Type, 
    QuarterName =   text, 
    isWeekend =     logical
  ], 
  List.Transform(
    List.Dates( #date( 2023, 1, 1 ), 18627, #duration( 1, 0, 0, 0 ) ), 
    each {
      _, 
      Date.Year(_), 
      Date.Month(_), 
      Date.MonthName(_), 
      Date.Day(_), 
      Date.DayOfWeek(_, 1), 
      Date.DayOfWeekName(_), 
      Date.QuarterOfYear(_), 
      Text.From( Date.QuarterOfYear( _ ) ) & "Q", 
      if Date.DayOfWeek( _, 1 ) > 4 then true else false
    }
  )
)

4. Using #table function to create a calendar in a single step

In this example:

The table structure is defined with specific data types for each column in lines 2-13.
A list of dates is generated starting from January 1, 2023, for a specified number of days on line 15.
Each date is transformed to create detailed columns, such as year, month, day of the week, and quarter, including a logical column to indicate if the day is a weekend. You can see this in line 18-26

Other functions related to #table are:

Contribute » | Contributors: Rick de Groot, Štěpán Rešl
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/sharptable