Power Query: Use Table.Combine to Append

Key insights

• Table.Combine — Power Query M function that takes a list of tables and returns one stacked table.
  Use the optional second argument to set the exact output columns so your master table keeps a stable schema even if sources change.
• Append Queries (graphical) — Use Home > Append Queries or Append Queries as New in Power Query Editor to combine tables without writing code.
  Choosing "Append as New" creates a safe master query and leaves original queries intact for reuse.
• Column matching by name — Power Query aligns columns by their names, not by position, and fills missing fields with nulls.
  Standardize column names across source tables to avoid unexpected nulls and misaligned data.
• Fix data types & inconsistent field names — Set correct data types in each source so types flow through to the combined table.
  Rename or normalize inconsistent field names before appending; the video shows both editing the source step and editing the M code to add tables to an append.
• Reference vs Duplicate — Use Reference to create a linked query that updates with its source; use Duplicate to create an independent copy.
  Reference keeps refresh logic simple; Duplicate isolates changes when you need a fixed snapshot.
• Practical examples and workflows — The video demos importing multiple Excel tables and a tab-delimited text file, using & and Table.Combine to append, and sorting with Table.Sort.
  It also shows loading options (Create Connection Only vs load to worksheet) and a final summary to apply in recurring consolidations.

Overview of the Video

In a focused tutorial, ExcelIsFun explains how to consolidate multiple tables into a single master table using Microsoft Power Query. The video highlights the differences between the graphical Append Queries command and the M-language function Table.Combine, and it provides concrete examples to demonstrate each approach. As a result, viewers gain both a step-by-step interface workflow and an understanding of the underlying M code logic.

Moreover, the author walks through six practical examples that emphasize common challenges, such as inconsistent column names and type propagation. The tutorial also includes guidance on importing Excel and tab-delimited files, saving queries as connections, and loading results to worksheets. Consequently, the video serves as a compact, hands-on lesson for users who consolidate recurring reports or regional exports.

Core Concepts Demonstrated

First, ExcelIsFun contrasts vertical appending with horizontal merging, clarifying that append operations stack rows while merge operations join columns by key values. He then introduces the M function Table.Combine, which takes a list of tables and returns one combined table, and he shows how this function maps columns by name rather than by position. As a result, viewers see why column names matter when consolidating data from varied sources.

Next, the tutorial demonstrates how the optional second argument in Table.Combine lets you specify the output column order and structure, which can enforce consistency even when source tables change. The presenter also shows the simpler UI route through Append Queries for users who prefer a graphical editor. Therefore, the video balances code-level control with interface convenience so users can choose the approach that fits their comfort level.

Finally, ExcelIsFun covers utilities such as the ampersand operator & as a quick shorthand for appending queries, and he highlights the importance of the Table.Sort function when ordered output matters. These demonstrations illustrate both common and advanced techniques in a compact sequence, and they set the stage for troubleshooting real-world data consolidation tasks.

Managing Schema Differences and Data Types

A significant portion of the lesson addresses the frequent problem of inconsistent field names across source tables, which can break a clean append. The presenter shows how to normalize headers in the source queries so that columns align correctly once appended, and he explains that Power Query fills absent fields with null values when column names do not match. Consequently, maintaining consistent column names upstream reduces surprises downstream.

In addition, ExcelIsFun demonstrates that fixed data types in source tables flow through to the appended result, which can be both helpful and limiting. For example, enforcing a numeric type early prevents text values from slipping into numeric columns, but it can also make troubleshooting harder when source inputs vary. Therefore, the video advises a thoughtful balance: validate and set types where appropriate, but avoid rigid enforcement before you understand the variability of incoming files.

Moreover, the tutorial explains how specifying the column list in Table.Combine can prevent schema drift when new or missing fields appear over time. While that option increases reliability, it carries the tradeoff of requiring maintenance whenever the intended output schema legitimately changes. Thus, teams must weigh stability against flexibility when fixing the master schema.

Workflow Choices and Tradeoffs

The video contrasts editing M code directly versus using the UI to change append steps, noting important tradeoffs between speed and control. Editing the M formula bar can quickly add a new table to a combine step and is more efficient for advanced users, while the graphical editor offers safeguards and clearer visuals for less technical users. As a result, organizations often adopt a hybrid practice: use the UI for routine changes and M edits for systematic or repeatable adjustments.

ExcelIsFun also discusses the difference between creating a query Reference and creating a Duplicate, which affects upstream dependencies and refresh behavior. A reference links to the original query so changes propagate automatically, whereas a duplicate creates an independent copy that can diverge. Consequently, choosing between the two impacts maintainability and the risk of accidental changes.

Another practical tradeoff covered in the video is performance versus clarity when combining many tables. Table.Combine scales well conceptually, but enormous lists and very large source files can slow refresh times, so prefiltering or incremental loads may be necessary. Therefore, the tutorial encourages testing refresh times early and planning whether to consolidate at source or within the query layer.

Practical Tips and Conclusion

Practically, the author recommends loading intermediate queries as a connection only to keep Excel workbooks lean, and then choose specific queries to load to worksheets for reporting. He also shows how to sort the combined output and to maintain a clear step order so future edits remain straightforward. These tips emphasize reproducibility and predictable behavior in ongoing reporting scenarios.

In conclusion, ExcelIsFun delivers a concise, example-driven guide to appending tables with a balance of UI and M-code instruction, illustrating common pitfalls and pragmatic solutions. For teams consolidating recurring files, the video offers immediate techniques and decision points, helping readers understand both the benefits and the tradeoffs of different approaches. Overall, it is a useful resource for anyone looking to build reliable master tables in Power Query.

Keywords

power query append tables, table.combine power query, append queries excel, combine tables power query, append multiple tables power query, create master table power query, table.combine function, power query append tutorial