#table

Updated on

#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

Creates a table value from columns and rows. The columns value can be a list of column names, a table type, a number of columns, or null. The rows value is a list of lists, where each element contains the column values for a single row.

Examples

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

To initiate an empty table, simply provide the #table function with two empty lists:

#table( { }, { } )

You can also generate a table with two columns, defining two rows of data. The examples below do not designate any column names or assign data types to a column.

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

/* You could also 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

In most instances, being more specific with your table’s structure can be beneficial. 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

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

For a more advanced example have a look at the following. The code in lines 2-13 then define the data types of each column in the order provided. Line 15 then generates a list of dates. And these dates are transformed into columns as specified in line 18-26.

#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

Other functions related to #table are:

BI Gorilla Blog

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