Power BI / Basic: analytical and modeling foundations / Lesson 3

Shaping, unpivot and quality ledgers

Transform wide exports into stable analytical rows while preserving grain and accounting for every accepted, rejected and intentionally removed record.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Explain unpivot as a change in row representation.
Separate duplicate observations from legitimate repeated business events.
Build row and value reconciliation checks.

State input and output grain

A spreadsheet export often has one product per row and separate month columns. That shape is convenient for human entry but awkward for filtering many periods consistently. Unpivoting turns selected column headings into attribute values and their cells into a value column. The new grain is product-period, so more rows are expected. A row-count increase is not automatically duplication. Write the before-and-after grain and identify the fields that must remain keys. If the input already contains totals beside detail months, exclude or classify those totals rather than unpivoting them into another apparent period.

Read a small unpivot

The first M expression creates one synthetic product with January and February amounts and unpivots those two columns. The intended result contains two product-month rows with values10 and20. It is a complete in-memory expression but remains unexecuted because no M engine is available. Decide whether the contract names exact period columns or treats all non-key columns as periods. The latter can accommodate new months but can also accidentally absorb a newly added comment column, so schema evolution needs explicit review rather than blind flexibility.

powerquery Explicit period-column unpivot

let
    Source = #table(type table [Product = text, Jan = number, Feb = number], {{"A", 10, 20}}),
    Long = Table.Unpivot(Source, {"Jan", "Feb"}, "Month", "Amount")
in
    Long

Expected rows are A/Jan/10 and A/Feb/20. No external source or Power Query execution is used.

Define duplicates before removal

Two equal amounts for the same product can be legitimate separate orders. Deduplication requires a business key and a rule for competing versions, not simply selecting every visible column and removing repeats. Conversely, a repeated extract batch can double-count facts even when a generated row number differs. Preserve source lineage and decide whether later corrections replace earlier records or represent new events. Sort order alone may not provide a stable winner when source execution changes. A quality ledger should state how many records were removed and why, making the transformation auditable.

Reconcile values and counts

A transformation should preserve the intended total unless an explicit rule changes scope. The second Python model unpivots a tiny dictionary and compares the total before and after. It also reports the new row count. This is an independent arithmetic oracle, not the M result. Real nulls deserve special attention because unpivot behavior can omit null-valued cells, so a simple products-times-months count may not hold. Retain a null fixture and an explicit expectation. Reconciliation should catch accidental total columns, dropped periods and duplicate batches, not merely confirm that a query returned something.

python Synthetic shaping reconciliation

wide = {"A": {"Jan": 10, "Feb": 20}, "B": {"Jan": 5, "Feb": 15}}
long = [(product, month, value) for product, months in wide.items() for month, value in months.items()]
print(len(long))
print(sum(value for _, _, value in long))
print(sum(sum(months.values()) for months in wide.values()))

Expected output

4
50
50

Four rows and matching totals50 demonstrate this fixture only. Null omission and M evaluation are not simulated.

Keep a quality ledger

A useful ledger records source rows, accepted rows, rejected rows, intentionally filtered records and key/value reconciliation. Keep filters named by business meaning, such as excluding canceled lines, rather than an unexplained step number. Review column names and types after every shape-changing operation. Preview sampling and a successful refresh do not prove full data quality. This course executes no connector or M query. Its local reconciliation checks a synthetic transformation only. The final model still needs actual engine verification, source reconciliation and report-context testing before a number is treated as a reliable business result.

Common errors and fixes

Row growth after unpivot is labeled duplication

Why it happens: The output grain was not compared with the input grain.
How to fix it: Define product-period keys and reconcile expected cells.
Prevent it next time: Record grain changes beside shape-changing steps.

Legitimate repeated orders are removed as duplicates

Why it happens: Equal displayed values replaced a business-key definition.
How to fix it: Deduplicate only under the approved key and version rule.
Prevent it next time: Keep rejection/removal counts and source lineage.

A new comment column becomes an apparent month

Why it happens: All non-key columns were unpivoted without schema review.
How to fix it: Constrain or classify intended period columns explicitly.
Prevent it next time: Validate schema evolution before recurring refresh.

Check your understanding

1. Why should an annual total column be treated separately from monthly detail?

Show worked solution

It summarizes the same observations and would double-count them if unpivoted as an additional period alongside all months. Exclude it from analytical facts or retain it as a separately labeled reconciliation value. Define the expected relationship between monthly detail and the annual figure rather than mixing both at one fact grain.

2. What should a quality ledger explain when revenue falls after cleaning?