Business Intelligence with Power BI

L03 · Power Query II: Combine

Practice — Hot · The Duplication Accusation

Skills focus. Everything from Module 1, pointed at a live dispute: append, profiling, dedup judgment, reconciliation — and knowing when Remove Duplicates is the wrong tool.

Ungraded, open-ended. The solution shows one good investigation, not the only one.

Scenario

At the half-year review, an operations manager challenges your combined sales table in front of the room:

"May's file has 5,304 rows and January's has 3,903. Thirty-six percent bigger? I think the export system double-wrote rows in May — and I can prove it: run a duplicate check on the combined table. I did. It finds duplicates. Your half-year numbers are inflated and I don't trust this table."

The manager is right about one thing: a full-row duplicate check on your 26,139-row combined table does find duplicate rows. Your job is to work out what they actually are, whether anything is inflated, and what — if anything — to fix.

Requirements

  1. Reproduce the finding. How many exact duplicate rows does the combined table contain? (Count rows before and after a Remove Duplicates on a copy of the query — never the working one.)
  2. Interrogate the claim of May inflation. If the export system double-wrote May, what would the distinct order counts per file show? Check them. Does any order appear in two different monthly files?
  3. Explain the duplicates you did find. Look at a few of them. What column is this export missing that makes two legitimate rows able to look identical? (Think about what happens when a customer puts the same lamp in their cart twice.)
  4. Reconcile. Finance's published H1 revenue figure is $11,515,008.14. Does the combined table support it — with the "duplicates" left in, or with them removed?
  5. Write the verdict — four or five sentences to the review meeting: is May inflated, what were the duplicates, should Remove Duplicates be part of this recipe, and what should be requested from IT for future exports.
Hint — distinguishing "double-written" from "genuinely identical"

A double-write duplicates rows at random, inflating totals against any independent reference. Genuinely identical line items exist for a boring reason: the export has no line-number column, so an order containing the same product twice at the same price produces two indistinguishable rows. One of these two stories survives a reconciliation against finance's independent total. The other does not.

Reflection

  • In Lesson 2, Remove Duplicates (all columns) was the correct first fix. Here it would be a mistake. State the property of the data — not the button — that flipped.
  • The manager ran a real check and got real duplicates, and was still wrong. What did their investigation lack that yours had?