Appending and merging queries

Intermediate Power Query in Excel

Lyndsay Girard

Performance Analytics Consultant

Combining datasets in Power Query

Illustration of combining multiple datasets into one

Consolidates data from multiple sources
Saves time by reducing manual manipulation (e.g., VLOOKUP)
Enriches larger datasets by including additional columns from related tables

Principles of append vs. merge

Append

Stacking datasets of a shared column structure vertically.

Illustration depicting multiple datasets before appending data

Merge

Combining datasets horizontally based on common column(s).

Illustration depicting two datasets before merging data

Principles of append vs. merge

Append

Stacking datasets of a shared column structure vertically.

Illustration depicting multiple datasets after appending data

Merge

Combining datasets horizontally based on common column(s).

Illustration depicting two datasets before merging data

Append in practice

Ensure that the columns in the tables being combined have the same:
- Name
- Data Type

Sample datasets before being appended

Append in practice

Ensure that the columns in the tables being combined have the same:
- Name
- Data Type

Sample datasets after being appended

Diving deeper into the merge operation

Illustration of two datasets being merged together

Merged data is relative to the current selected query
Unmatched records may be included or excluded from output

Join types

Type of join determines how records are combined or excluded based on matching criteria

Illustration depicting the various join types including left, right, full, inner join etc.

Merge in practice: left outer join

Ensure the shared column (key) to match on is of the same data type
Preview the results to ensure that the merging logic is accurate

Sample datasets before being merged

Merge in practice: left outer join

Ensure the shared column (key) to match on is of the same data type
Preview the results to ensure that the merging logic is accurate

Sample datasets after being merged with left outer join

Merge in practice: full outer join

Ensure the shared column (key) to match on is of the same data type
Preview the results to ensure that the merging logic is accurate

Sample datasets after being merged with left outer join

Special join conditions

Exact Match: Identical matches between data elements
Fuzzy Match: Allows for flexible and approximate matching

Sample datasets to demonstrate special join conditions

Special join conditions

Exact Match: Identical matches between data elements
Fuzzy Match: Allows for flexible and approximate matching
- Ignore case
- Match by combining text parts

Sample datasets to demonstrate special join conditions with fuzzy matching

Dataset

Illustration of a hospital exterior building emergency department with an ambulance in front

Real-world hospital dataset (de-identified electronic health record data)
Emergency department visits to the Yale New Haven Health System from March 2014 to July 2017
Patient demographics and registration information, triage assessments, medications, and other related data

¹ journals.plos.org/plosone/article?id=10.1371/journal.pone.0201016

Let's practice!

Intermediate Power Query in Excel