Table.Combine - Table Function

Table.Combine is a Power Query M function that merges a list of tables, with the resulting table having a row type structure defined by specified columns or by a union of input types. The function returns a combined table.

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

Syntax

Table.Combine(
   tables as list,
   optional columns as any,
) as table

Argument	Attribute	Description
tables		The table names provided as text values in a list.
columns	optional	Allows you to select columns to return after combining the tables.

Description

The Table.Combine function merges multiple tables into a single table. If the tables have the same columns, it appends the data from each table. In cases where tables have different columns, it fills in missing columns with null values.

This optional columns argument allows you to specify which columns to include in the resulting table after combining. You have three ways to define this:

Using a Number: If you specify a number, say 3, the function returns the columns with the actual names of: Column1, Column2, and Column3 in the output and loses their data types.
Listing Column Names: Alternatively, you can define the columns to include by specifying their names. This approach, which involves listing the column names like { "Product", "Sales" }, selects only those columns. However, it’s important to note that this method loses the original data types of the specified columns.
Defining a Table Type: For a more comprehensive control, specifying a table type is recommended. This involves defining a type table with both the desired columns and their respective data types. An example would be: type table [Product = text, Sales = Int64.Type]. This method also retains the respective data types of each column.

Examples

For the following examples we’ll work with these three tables:

Combining Tables with Identical Columns

Let’s start with the basics. Suppose you have two tables, Table1 and Table2, with an identical structure (same columns). You can easily combine these two using:

Table.Combine( { Table1, Table2 } )

Table.Combine Performs a Union on Tables with Identical Columns in Power Query M

This operation is similar to a SQL UNION, but with a twist. Unlike SQL, where columns need to be in the same order, Table.Combine in Power Query M matches columns based on their names, not their order. This flexibility allows you to merge tables even if their columns are ordered differently.

Dealing with Tables Having Different Columns

Now, let’s say there’s a third table, Table3, with an additional column, ‘Margin’. Even if this column doesn’t exist in Table1 or Table2, Table.Combine can still merge these tables. The function fills the missing column in the other tables with null values:

Table.Combine( { Table1, Table2, Table3 } )

Table.Combine Returns Null Values for Missing Column in Power Query M

This feature help combining tables of varying structures without compromising the integrity of your data.

Selecting Specific Columns to Combine

There might be cases where you don’t need all the columns from the tables you’re merging. Table.Combine allows you to specify which columns to include:

Table.Combine( { Table1, Table2, Table3 }, {"Product", "Sales"} )

Table.Combine Combines Tables and Selects Specific Columns in Power Query M

This command tells Power Query M to keep only the ‘Product’ and ‘Sales’ columns in the combined table. However, be mindful that this method might lead to the loss of data type information.

Retaining Column Type Information

To retain the data types while selecting specific columns, you can explicitly specify them:

Table.Combine( 
  { Table1, Table2, Table3 }, 
  type table [ Product = text, Sales = Int64.Type] 
)

Alternatively, you can use field projection to select columns and retain data types:

Table.Combine( { Table1, Table2, Table3 } )[[Product],[Sales]]

Table.Combine Selects Columns and Retains Data Types in Power Query M

This method ensures that the combined table maintains the specified data types for each column.

Dynamic Data Type Retention

For a more advanced, dynamic approach that automatically retains data types from the original tables, you can use a combination of type functions like Type.TableRow, Type.RecordFields and Type.ForRecord, along with Record.SelectFields. This method dynamically extracts data types for the specified rows:

let
  // Retrieve the record-type of a table row
  OriginalRecordType = Type.TableRow ( Value.Type ( Table1 ) ), 
  // Split the type into types for each field
  TypeRecordFields   = Type.RecordFields ( OriginalRecordType ), 
  // Select the desired Columns
  SelectFields       = Record.SelectFields ( TypeRecordFields, { "Product", "Sales" } ), 
  // Turn the record fields back into a record type
  RecordType         = Type.ForRecord ( SelectFields, false ), 
  // Select desired columns and retain their data type
  CombineTables      = Table.Combine ( { Table1, Table2, Table3 }, type table RecordType )
in
  CombineTables

This code retrieves data types from Table1 and applies them to the ‘Product’ and ‘Sales’ columns in the combined table.

Putting It All into Practice

To try these examples, use the following Power Query M code. This code sets up three tables and demonstrates different ways to combine them:

let
    Table1 = 
      #table( 
        type table[ ProductKey = Int64.Type, Product = Text.Type, Sales = Int64.Type ],
        {  { 1, "Product A", 150 }, { 2, "Product B", 200 }  }  ),
    Table2 = 
      #table( 
        type table[ ProductKey = Int64.Type, Product = Text.Type, Sales = Int64.Type ],
        { { 3, "Product C", 175 },{ 4, "Product D", 220 } } ), 
    Table3 = 
      #table( 
        type table[ ProductKey = Int64.Type, Product = Text.Type, Sales = Int64.Type, Margin = Int64.Type ],
        { { 5, "Product E", 185, 85},{ 6, "Product F", 240, 110 } } ),
  CombineConsistentTables   = Table.Combine ( { Table1, Table2 } ), 
  CombineInconsistentTables = Table.Combine ( { Table1, Table2, Table3 } ), 
  CombineAndSelectCols      = Table.Combine ( { Table1, Table2, Table3 }, { "Product", "Sales" } ), 
  CombineAndSelectColTypes1 = 
    Table.Combine (
        { Table1, Table2, Table3 }, 
        type table [ Product = text, Sales = Int64.Type ] ), 
  CombineAndSelectColTypes2 = Table.Combine ( { Table1, Table2, Table3 } )[[Product], [Sales]], 
  CombineAndSelectColTypes3 = 
    let
      OriginalRecordType = Type.TableRow ( Value.Type ( Table1 ) ), 
      TypeRecordFields   = Type.RecordFields ( OriginalRecordType ), 
      SelectFields       = Record.SelectFields ( TypeRecordFields, { "Product", "Sales" } ), 
      RecordType         = Type.ForRecord ( SelectFields, false ), 
      CombineTables      = Table.Combine ( { Table1, Table2, Table3 }, type table RecordType )
in
  CombineTables
in
    CombineAndSelectColTypes3

Learn more about Table.Combine in the following articles:

Join Types in Power Query
Power Query has different join types for merging queries. Mastering them allows you to easily retrieve new values, keep relevant ones or discard them.. » Read more

Other functions related to Table.Combine are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-combine