#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 anyDescription
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
}
)
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
}
)
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" }
}
)
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
}
)
)
Related functions
Other functions related to #table are:
