M Query Guide

Transformations

M Combining Tables

Combining Tables

M combining tables uses Table.Combine for appending data.

Introduction to Table.Combine

In the M language, combining tables is a common operation especially when dealing with datasets that need to be appended. The Table.Combine function in Power Query M is used to append rows from multiple tables into a single table.

Understanding the Syntax

The basic syntax for Table.Combine is as follows:

Here, tables is a list of tables you want to combine, and optional columns allows you to specify a list of columns to include in the combined table. If not specified, all columns from the tables are included.

Basic Example of Table.Combine

Let's consider a scenario where you have two tables, Table1 and Table2, and you want to combine them into a single table.

In this example, Table1 and Table2 are combined into a new table CombinedTable. The resulting table will have all the rows from both Table1 and Table2.

Combining Tables with Different Columns

If the tables you are combining have different columns, Table.Combine will include all unique columns from each table, filling in nulls where data is missing.

In the above code, Table3 has columns ID and Age, while Table4 has columns ID and Name. The combined result will have columns ID, Age, and Name, with nulls in rows where a column doesn't exist in the original table.

Using Optional Columns Parameter

The optional columns parameter allows you to specify exactly which columns should appear in the combined table, which can be useful if you want to filter out unnecessary data.

In this example, even though Table6 has an Age column, it will be excluded from the combined table because only the ID and Name columns are specified.

Conclusion

Understanding how to effectively use Table.Combine in M allows you to manage and manipulate your data seamlessly. Whether you are appending simple datasets or dealing with complex tables with varying structures, Table.Combine is a powerful tool to have in your data transformation toolkit.

Transformations

Next
Custom Columns