Power BI / Intermediate: context, quality and access / Lesson 10

Merge, append, folding and privacy

Combine prepared tables without multiplying facts, and distinguish source-side transformation from local evaluation and privacy decisions.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Compare append alignment with key-based merge.
Predict expanded join cardinality and reconcile totals.
Explain folding and privacy as connector-specific contracts.

Choose the combination operation

Append stacks rows from compatible tables, aligning columns by their names rather than their physical positions. Merge matches rows using selected key columns and a join kind. These operations solve different problems: monthly extracts can be appended, while a product attribute can be merged onto sales. Define the resulting grain before choosing either operation. A renamed column in one append input can create a separate column with nulls instead of filling the expected field. Matching header names in a merge are not required, but corresponding key types and composite-key order must represent the same identity.

Inspect expansion cardinality

A left merge preserves a left row before expansion by storing matches in a nested table. Expanding two right-side matches can create two output rows for that one fact. The first M example deliberately gives product A two attribute rows, so the expanded revenue sum becomes40 instead of the original30. It is an intentional bad-input illustration, not an approved model. An inner join can instead remove unmatched facts. Check both duplication and omission rather than assuming a successful merge preserves totals. Resolve duplicate keys under the business version rule; removing an arbitrary duplicate can silently choose the wrong historical attribute.

powerquery Unexecuted duplicate-match illustration

let
    Sales = #table(type table [Key = text, Amount = number], {{"A", 10}, {"B", 20}}),
    Products = #table(type table [Key = text, Label = text], {{"A", "x"}, {"A", "y"}, {"B", "z"}}),
    Joined = Table.NestedJoin(Sales, {"Key"}, Products, {"Key"}, "Product", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "Product", {"Label"})
in
    Expanded

Expected expanded amounts are10,10,20 across three rows. This M expression is not executed and intentionally demonstrates a key-quality defect.

Understand folding without assuming it

Query folding attempts to translate supported transformations into work performed by a capable data source. Other work is evaluated by Power Query. Connector support, source capabilities and the sequence of transformations influence the outcome. A local #table example has no remote source to fold to. Filtering early can reduce transferred data when the operation actually folds, but a convenient step name does not prove pushdown. Review the relevant diagnostics and source contract in an authorized environment. Do not execute a database query or load test merely to make this editorial example appear verified.

Preserve privacy boundaries

Combining sources can move values from one trust boundary into another during evaluation. Privacy levels help govern those combinations; they are not labels to disable whenever a refresh is inconvenient. A source that is public and a source containing private identifiers have different disclosure assumptions. Use synthetic data and document what may cross boundaries. The second program demonstrates join multiplication using public lists only, with no connector or privacy engine involved. A privacy-related error requires a justified source/combination design, not a universal instruction to ignore protections or paste private rows into a diagnostic dump.

python Synthetic join-multiplication oracle

sales = [("A", 10), ("B", 20)]
products = [("A", "x"), ("A", "y"), ("B", "z")]
expanded = [(key, amount, label) for key, amount in sales for product, label in products if key == product]
print(len(expanded))
print(sum(amount for _, amount, _ in expanded))

Expected output

3
40

Three rows and total40 expose the duplicated A fact. No source system or M engine is contacted.

Reconcile the final combination

Record source row counts, key uniqueness, unmatched rows, expanded row counts and amount totals. Use distinct-key and duplicate-key cases, an unmatched left key and a renamed append column. Keep source lineage sufficient to explain which input contributed each row. This course runs the list comprehension only; folding, privacy enforcement and actual Power Query evaluation remain untested. A complete combination review explains the intended grain and every changed total rather than trusting the presence of a merged column or a successful preview as evidence that the business facts remain unchanged.

Common errors and fixes

Revenue increases after expanding a product merge

Why it happens: Several right-side rows matched one fact key.
How to fix it: Resolve the dimension key/version contract and reconcile the expansion.
Prevent it next time: Check key uniqueness and totals before and after merges.

An appended field splits into two columns

Why it happens: Input schemas used different column names.
How to fix it: Align the documented schema before append and verify null counts.
Prevent it next time: Keep a schema-change fixture for recurring extracts.

Privacy checks are disabled to silence a combination error

Why it happens: Source trust and value movement were not reviewed.
How to fix it: Design an approved source-combination boundary with synthetic diagnostic evidence.
Prevent it next time: Treat privacy decisions as data-governance decisions.

Check your understanding

1. Why does a left merge not guarantee the expanded fact row count remains unchanged?

Show worked solution

The nested result can contain multiple matches for one left key. Expansion produces one row per match and repeats the left amount. Audit uniqueness and expected cardinality, then reconcile totals. The join kind describes matching and retention, not a promise that every right-side key is unique.

2. Can the local #table example establish that an early filter folds against a database?