Table.Combine
Stacks several tables with matching columns into one longer table.
Table.Combine(tables as list, optional columns as any) as tableReturns: A table with the rows of all the input tables.
What it does
Table.Combine puts tables on top of each other. Twelve monthly bank exports with the same layout become one table with every month's transactions. Columns are matched by name, so the files must use the same headings. A column that is missing from one file comes out blank for those rows.
Syntax and arguments
- tables: a list of the tables to stack, in braces. Required.
- columns: optional. Use it to control which columns are kept in the result. Leave it out for the usual stack.
Example
Two monthly bank exports, saved as separate tables in the workbook, are stacked into one table for the quarter.
let
January = Excel.CurrentWorkbook(){[Name = "Bank_Jan"]}[Content],
February = Excel.CurrentWorkbook(){[Name = "Bank_Feb"]}[Content],
Quarter = Table.Combine({January, February})
in
Quarter
The result has the transactions from both months. Use the same approach with more months in the list.
Things to watch
- Column names must match. A heading that differs by a space, such as "Amount " and "Amount", becomes two separate columns.
- Check the headers of each file before combining, using Table.ColumnNames.
- Set the types after the stack, since a column that was a number in one file and text in another can mix.
- Add a column with the source month to each file before combining, so the rows can be traced later.
- Query folding: stacking often does not fold across separate sources. Check View Native Query if the source is a database.
In business terms
Monthly exports stacked into a quarter or year is the basis for year-to-date reporting and for an annual review of transactions. A source-month column keeps each row traceable to its file.