Create Tables from Scratch in Power Query M (40+ Examples)

Tables have a fundamental place in Power Query. Most transformations you make are based on tables and mastering how they work helps you in your Power Query journey.

The most common way to retrieve tables in Power Query is to use a connector. You can import tables from a SQL database but also from things like Excel and CSV files.

Yet in this article, you will learn how to create them from scratch. Understanding how to create tables will help you tremendously when you want to generate new data and combine this with other tables.

1. Functions for Creating Tables

Before we get started you can find a summary of the functions you will learn about. The summary does not contain all optional parameters of the functions, but helps in getting an overview of how the functions compare.

All of the below functions can help you in creating 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.FromLists 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, list of column names, or a Tabletype.

The first 2 determine column names, 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 can use the Record.FieldValues splitter function to indicate this. Other available options deserve a separate blogpost.

= 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 behavior 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 behavior 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 list, the second argument is optional. The optional argument can contain a number of columns, 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 a 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 a 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"] )
// Returns a 2 column table with columns "Name" and "Value"
// You can't define the column names for records

= Table.FromValue( 
     [1 = "Apple", 2 = "Prume" ] 
) 
// Returns a 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 ] 
)

As this post showed, there are different approaches to creating tables. All the functions have their particular requirements and I hope this post inspires you to play around with them.

Enjoy Power Query!

Recommend Reading

>>> Power Query - Foundations <<<List Functions (200+ examples)Text functions (150+ examples)Creating Tables from Scratch (40+ examples)Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

6 thoughts on “Create Tables from Scratch in Power Query M (40+ Examples)”

  1. Hi Rick,
    Being nerdy, following example is technically correct, but logically contradicts to ones given above – ProductKey is same for both records.

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

    PS. Overall stuff you publish is very clear, practical and straightforward to understand.

    Reply
    • 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! 😉

      Reply
  2. 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.

    Reply
    • 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!

      Reply
      • 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.

        Reply
        • That’s really cool! I will share an article on records in the future too. Not sure when, but it’s coming for sure.

          Reply

Leave a comment