Tables have a fundamental place in Power Query. Most data transformations are based on tables; mastering how to create them from scratch can help you solve many challenges.
In this article, we will show you 6 different ways to create table values using Power Query, using examples and explanations for each method. We’ll show you how to create tables using records, lists, rows and values. With more than 40 examples, this post is an elaborate guide that will help you understand how to create tables in Power Query, even if you are a beginner. Let’s get started!
Table of contents
1. Functions for Creating Tables
There are many different functions that can help you make tables from scratch. In this chapter, I will give you an overview of all these functions. You’ll learn about the different ways to make tables using different types of data. It might seem a bit tricky at first but don’t worry, I’ll explain everything in a way that’s easy to understand. Let’s get started and make some tables!
Below, you can find a summary of the functions that create tables in the M language. It does not contain all optional parameters of the functions but does provide an overview of how the functions compare.
All of the below functions can help you manually create tables in Power Query. Most of them have a way to input Colum Names, and they all take the column and row input in their own way:
#table(
ColumnNames, // as text, list or table type
List of Lists // each list represents a row
)
Table.FromRecords(
List of Records, // each record represents a row
ColumnNames, // as text, list or table type
)
Table.FromList(
List of Values or Records, // each value/record represents a row
Optional Splitter, // depends on the first argument
ColumnNames, // as text, list or table type
)
Table.FromColumns(
List of Lists, // each list represents a column
ColumnNames, // as text, list or table type
)
Table.FromRows(
List of Lists, // each list represents a row
ColumnNames, // as text, list or table type
)
Table.FromValue(
Value as any, // this can be a regular value, list, record,
) // list of records
With this overview, it’s now time to delve into the details of each function. How do you create tables from scratch?
2. #table Constructor
One of the easiest ways to create a table is with the #table constructor. The general syntax for this function is:
#table(
columns as any,
rows as any,
) as any
To see how this constructor creates tables, have a look at the following examples.
#table(
null, // not specifying any column name
{
{ 1, "Apple" }, // determines values first row
{ 2, "Prume" } // determines values second row
}
) // Creates Table without defining Column Names
#table(
2, // specifying number of columns, no name
{
{ 1, "Apple" }, // determines values first row
{ 2, "Prume" } // determines values second row
}
) // Creates Table with n number of columns, but no Column Names
// Note that if the number of columns is wrong, you get an error
#table(
{ "ProductKey", "Product" }, // defines column names
{} // returns no rows
) // Creates table with only Column Names, no rows.
#table(
{ "ProductKey", "Product" }, // defines column names
{
{ 1, "Apple" }, // determines values first row
{ 2, "Prume" } // determines values second row
}
) // Creates a table without defined Data Types.
#table(
type table[ ProductKey = Int64.Type, Product = Text.Type ],
{
{ 1, "Apple" },
{ 2, "Prume" }
}
) // Creates a table with specified Data Types and Column Names
3. Table.FromRecords
Another way to create a table out of thin air is by transforming a record into a table with the Table.FromRecords function. Within this function, each record represents a row in the table. The syntax for this function is:
Table.FromRecords(
records as list,
optional columns as any,
optional missingField as nullable number
) as table
Within this function, you can input a MissingField.Type of Missing.FieldError (used by default) or MissingField.UseNull. The latter returns a null when a record misses a value that other records have.
The code that creates simple tables then looks as follows:
Table.FromRecords(
{
[ ProductKey = 1, Product = "Apple" ] , // first record
[ ProductKey = 2, Product = "Prume" ] // second record
}
)
// Creates a table without defined column types
Table.FromRecords(
{
[ProductKey = 1, Product = "Apple"],
[ProductKey = 2, Product = "Prume"],
[ProductKey = 3] // This record misses Product
}
)
// Creates table with error on table row 3. By default the function
// returns a missing values error , identical to MissingField.Error
Table.FromRecords(
{
[ ProductKey = 1, Product = "Apple" ] ,
[ ProductKey = 2, Product = "Prume" ] ,
[ ProductKey = 3 ]
},
null,
MissingField.UseNull // returns null for missing fields
)
// Identical to previous table, but shows null for missing values
Table.FromRecords(
{
[ ProductKey = 1, Product = "Apple" ] ,
[ ProductKey = 2, Product = "Prume" ]
},
type table[ProductKey = Int64.Type, Product = Text.Type ]
)
// Creates a table with specified column types
Table.FromRecords(
{
[ Product = "Apple", ProductKey = 1 ] ,
[ ProductKey = 2, Product = "Prume" ]
},
type table[ProductKey = Int64.Type, Product = Text.Type ]
)
// Creates a table with the values in their respective column names.
An important note to end on. Within the Table.FromRecords function, the order of column names in the provided records does not matter. This is different from the Table.FromList function, where the order does matter.
4. Creating Tables from Lists
You can create a table from lists with two functions. Both Table.FromList and Table.FromColumns can transform lists into a table, but they have their own approach.
4.1. Table.FromList: Single Column
The first function we will look at is Table.FromList. This function turns a list into a table by applying the optional splitter function. You can use the 3rd argument to input a number of columns, a list of column names, or a Tabletype.
The first 2 determine column names, and the TableType can also determine the data type of a column. The 4th argument returns a default value when column input is missing.
Table.FromList(
list as list,
optional splitter as nullable function,
optional columns as any,
optional default as any,
optional extraValues as nullable number
) as table
The following examples create a single-column table by using lists with values.
Table.FromList(
{ "Apple", "Prume" } // specifies column values
)
// Creates table with name Column1, no data type
Table.FromList(
{ 1, 2 } // specifies column values
)
// Returns error, the function assumes text values for regular lists.
Table.FromList(
{ "Apple", "Prume" },
null, // specifies optional splitter
{ "Product" } // defines column name
)
// Creates table with a defined name, no data type
Table.FromList(
{ "Apple", "Prume" },
null,
type table[ Product = Text.Type ] // specifies column type and name
)
// Creates table with a defined column name and type
4.2. Table.FromList: Multi Column
To create multi-column tables, the Table.FromList offers several options. You can either provide a list or record within a list. Then, Power Query needs to know which values to extract from these objects.
For records, you can use the Record.FieldValues splitter function to indicate this. Other available options deserve a separate blog post.
Table.FromList(
{ "1, Apple", "2, Prume" }
)
// Creates table without defined Names or Data Types
// By default the function splits lists by Comma
Table.FromList(
{
[ ProductKey = 1, Product= "Apple" ], // defines list
[ ProductKey = 2, Product= "Prume" ] // of records
},
Record.FieldValues, // function splits the records
{"ProductKey", "Name"} // list defines column names, no data types
)
// Creates table without defined Data Types.
Table.FromList(
{
[ ProductKey = 1, Product = "Apple" ],
[ ProductKey = 2, Product = "Prume" ]
},
Record.FieldValues,
type table[ ProductKey = Int64.Type, Name = Text.Type ]
)
// Creates table with defined column names and types
Now, onto some more interesting behaviour of Table.FromList. When you provide the function with a list of records, the naming within those records does not make a difference. To define the name, you use the 3rd argument instead.
Table.FromList(
{
[ x = 1, y = "Apple" ], // x and y not returned as column name
[ x = 2, y = "Prume" ] // x and y not returned as column name
},
Record.FieldValues,
{ "ProductKey", "Product" } // defines column names
)
// Resulting table is identical to previous example
// Record names are defined in the third argument.
// Not in the records themselves (x & y in line 3-4)
And even though the column names in the records of argument 1 don’t impact the resulting column names, the order of columns does influence the output.
This behaviour is different from the Table.FromRecords function. The following 2 examples do not provide the same results:
Table.FromList(
{
[ ProductKey = 1, Product = "Apple" ],
[ ProductKey = 2, Product = "Prume" ]
},
Record.FieldValues,
type table[ ProductKey = Int64.Type, Name = Text.Type ]
)
Table.FromList(
{
[ Product = "Apple", ProductKey = 1 ],
[ ProductKey = 2, Product = "Prume" ]
},
Record.FieldValues,
type table[ ProductKey = Int64.Type, Name = Text.Type ]
)
4.3. Table.FromColumns
Even though the function name suggests otherwise, the Table.FromColumns function also turns lists into a table. With this function, each list represents a column. When one column has more values than others, the missing values get the value ‘null’.
The syntax for the function is:
Table.FromColumns(
lists as list,
optional columns as any
) as table
You can create a table with Table.FromColumn in the following way:
Table.FromColumns(
{
{1, 2}, // Values for the 1st column
{"Apple", "Prume"} // Values for the 2nd column
}
)
// Creates a table, column names are Column1 and Column 2
Table.FromColumns(
{
{ 1, 2 },
{ "Apple", "Prume" }
},
{ "ProductKey", "Product" } // specifies column names
)
// Creates a table with column names, but undefined column types
Table.FromColumns(
{
{ 1, 2 },
{ "Apple", "Prume" }
},
type table[ ProductKey = Int64.Type, Product = Text.Type ]
)
// Creates a table with column names and defined data types
Table.FromColumns(
{
{ 1, 2, 3 }, // specifies 3 column values
{ "Apple", "Prume" } // specifies 2 column values
},
type table[ ProductKey = Int64.Type, Product = Text.Type ]
)
// Missing column values in the 'Name' column show as 'null'
5. Table.FromRows
Another function that creates tables is Table.FromRows. It has 2 arguments. The first argument needs the rows as a list, the second argument is optional. The optional argument can contain a number of columns, a list of column names or a table type. The syntax is:
Table.FromRows(
rows as list,
optional columns as any
) as table
Some easy examples are:
Table.FromRows(
{
{1, "Apple"}, // values for first row
{2, "Prume"} // values for second row
}
)
// Creates table with 2 columns: Column1 and Column2
Table.FromRows(
{
{1, "Apple"},
{2, "Prume"}
},
2 // creates 2 columns
)
// Creates table that is identical to the previous one.
Table.FromRows(
{
{1, "Apple"}, // values for first row
{2, "Prume"} // values for second row
},
{"ProductKey", "Product"}
)
// Returns table without data types
Table.FromRows(
{
{1, "Apple"},
{2, "Prume"}
},
type table[ ProductKey = Int64.Type, Product = Text.Type ]
)
// Returns table with data types
6. Table.FromValue
A very versatile function is the Table.FromValue function. The syntax for this function is:
Table.FromValue(
value as any,
optional options as nullable record
) as table
In the optional parameter, you can indicate the Column name, but only for lists and scalar values. Some practical examples to see it in action are:
Table.FromValue( 1 ) // Returns 1 column table of type number
Table.FromValue( "a", [ DefaultColumnName = "Letter" ] )
// Returns 1 column with the name 'Letter' and type any.
Table.FromValue( { 1, 2 } ) // Returns 1 column of type any with 2 rows
Table.FromValue( { true, false }, [ DefaultColumnName = "True or False" ] )
// Returns 1 column with the name 'True or False' and type any.
Table.FromValue( [1 = "Apple"] )
// Table with 2 columns: "Name" & "Value". Can't define the column names in records
Table.FromValue( [1 = "Apple", 2 = "Prume" ] ) // Returns 2 column table with 2 rows
Inputting a list of objects (lists, records or tables) returns a table with a single column that contains those objects:
Table.FromValue(
{
[ ProductKey = 1, Product = "Apple" ],
[ ProductKey = 2, Product = "Prume" ]
}
)
// Returns a single column containing 2 records
Table.FromValue(
{
{"ProductKey", 1 } ,
{"ProductKey", 2 }
}
)
// Returns a single column containing 2 lists
7. Comparing Functions
Some of the functions have a lot of overlap. Single-column tables can be created with the following very similar expressions:
Table.FromValue( { "Apple", "Prume" }, [DefaultColumnName = "Product"] )
Table.FromList( { "Apple", "Prume" } , null, {"Product" })
Table.FromList and Table.FromRecords can take very similar input containing records, and their output then is identical:
Table.FromList(
{
[ProductKey = 1, Name = "Apple"], // defines values
[ProductKey = 2, Name = "Prume"]
},
Record.FieldValues, // the splitter for the records
type table[ProductKey = Int64.Type, Product = Text.Type ]
)
Table.FromRecords(
{
[ ProductKey = 1, Product = "Apple" ] ,
[ ProductKey = 2, Product = "Prume" ]
},
type table[ProductKey = Int64.Type, Product = Text.Type ]
)
Also, notice how similar the #table constructor and the Table.FromRows function are:
#table(
type table[ Product = Text.Type ] ,
{ {"Apple"}, {"Prume"} }
)
Table.FromRows(
{ {"Apple"}, {"Prume"} },
type table[ Product = Text.Type ]
)
Conclusion
That’s it! Now you know how to create tables in Power Query using different methods. You’ve learned how to create tables using the #table constructor, records, lists, rows and values. All the functions have their particular requirements, and I hope this post inspires you to play around with them. With these templates, manually creating tables should be easy. And I hope this post helped you understand how to create tables in Power Query.
Remember, most data transformations are based on tables, so mastering how to create them from scratch can help you solve many challenges. Keep practising and experimenting, and you’ll become a Power Query pro in no time! If you have any questions or other methods that you use, feel free to share them in the comments below.
Enjoy Power Query!
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Hi Rick, super compact posts created by you. However, I have an unsolved problem. I have a filter table in Power BI (e.g. column name – “Letter” and in it one row each with A, B, C, X, Y,…). How can I now take over the value “C” as a list, if I have selected row 3 in the filter table? In Power BI this is done with “Selectedvalue”. Is there a corresponding function in M-Query for this? Greetings Helmut
Hi Helmut,
One option you could try is go for something like:
In this case MyTable is the name of the table your refer to. Note that the code only works when there is a unique row that matches with the filter on the Letter Column being equal to letter “A”.
Hi Rick,
Being nerdy, following example is technically correct, but logically contradicts to ones given above – ProductKey is same for both records.
PS. Overall stuff you publish is very clear, practical and straightforward to understand.
You keep me sharp Konstantin. I now made sure all examples use an increasing sequence for the ProductKey. And thanks for your kind words, I enjoy delving into these topics and summarizing how they work. For others to learn, but also for myself to look back at sometimes.
Enjoy! 😉
Hi, Rick, thank you so much for this elaborate post. Very important part to me was how table type can be specified in the moment when table is created rather than creating a table without table type and then transform column types in next step.
I saw in the documentation that this should be possible but could not get how the syntax should look like.
I like how you can compress the information so that it is not too wordy but unlike the official documentation, one can still clearly understand how functions can be used.
Prorok, thanks so much for leaving those kind words. I’m happy my post helps you understand the syntax better when creating tables.
I hope to be releasing more of these type of posts, also for other items like Records. You can already find one for Lists posted earlier at https://gorilla.bi/power-query/complete-guide-to-lists/. I’m confident you will also enjoy that one.
Enjoy!
I already did. Your post on lists was an eye opener on how versatile this data type is. I have benefited from that one as well.
Lately, I was introduced to records and saw how I can use them to solve some problems that seemed unsolvable with my knowledge of Power Query. But then someone on PowerBi community forums pointed to a solution of one of my problems using records. I realized that records are another door that has been closed to me but there is a lot of interesting things behind. So I am looking forward to your future post on records.
That’s really cool! I will share an article on records in the future too. Not sure when, but it’s coming for sure.
Edit: you can find the record post right here: https://gorilla.bi/power-query/records/