Table.Combine

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

Description

Returns a table that is the result of merging a list of tables, tables. The resulting table will have a row type structure defined by columns or by a union of the input types if columns is not specified.

Examples

Merge the three tables together.

Table.Combine( {
    Table.FromRecords( {[CustomerID = 1, Name = "Bob", Phone = "123-4567"]} ),
    Table.FromRecords( {[CustomerID = 2, Name = "Jim", Phone = "987-6543"]} ),
    Table.FromRecords( {[CustomerID = 3, Name = "Paul", Phone = "543-7890"]} )
} )

 /* Output: 
Table.FromRecords( {
    [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
    [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
    [CustomerID = 3, Name = "Paul", Phone = "543-7890"]
} )
 */

Merge three tables with different structures.

Table.Combine( {
    Table.FromRecords( {[Name = "Bob", Phone = "123-4567"]} ),
    Table.FromRecords( {[Fax = "987-6543", Phone = "838-7171"]} ),
    Table.FromRecords( {[Cell = "543-7890"]} )
} )

 /* Output: 
Table.FromRecords( {
    [Name = "Bob", Phone = "123-4567", Fax = null, Cell = null],
    [Name = null, Phone = "838-7171", Fax = "987-6543", Cell = null],
    [Name = null, Phone = null, Fax = null, Cell = "543-7890"]
} )
 */

Merge two tables and project onto the given type.

Table.Combine( 
    {
        Table.FromRecords( {[Name = "Bob", Phone = "123-4567"]} ),
        Table.FromRecords( {[Fax = "987-6543", Phone = "838-7171"]} ),
        Table.FromRecords( {[Cell = "543-7890"]} )
    },
    {"CustomerID", "Name"}
 )

 /* Output: 
Table.FromRecords( {
    [CustomerID = null, Name = "Bob"],
    [CustomerID = null, Name = null],
    [CustomerID = null, Name = null]
} )
 */

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:

BI Gorilla Blog

Last update: August 28, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-combine
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.